Applied Underwriters Business Analyst Take-Home Project

Thank you for reading. Please see .pptx for a neater presentation of the above code and visualizations.

Prompt:

Currently, patients with a certain category of back injuries usually take a lifelong prescription of powerful pain medication. This medication is known to decrease life expectancy. Trials of a new surgical procedure have been conducted on some patients. If successful, the surgery immediately rids the patient of the ongoing need for medication. The surgery can only be attempted once. If the surgery is unsuccessful then there are no new adverse effects but the patient must continue on medication as before.

You have separately been provided with credentials to connect to a VPN and a remote machine. You may use any software on the machine. Sufficient software for this project is available there, as are the source data tables in FoxPro (.dbf) format. The source data tables are in a directory on the desktop of the test machine. Your answers to Part I should be loaded into the provided tables QuestionA and QuestionB on the local SQL Server database Analyst_Project. Any files used (including all of your code) should be stored in the directory on the desktop named Results, along with your answers for Part II. Assume today’s date is January 1, 2018.

Be prepared to talk about your results in a short 20 minute technical interview. Any experience you have with R, SQL Server, Excel or FoxPro is likely to be an advantage. However, we don’t require you to have prior experience, and this project has been completed successfully by candidates with no prior knowledge of SQL, R or FoxPro. Good Luck!

# dbf.Patients <- read.dbf(file = "./data/PATIENTS.dbf") # read.dbf doesn't work well here; using 'Export' feature from FoxPro instead
# dbf.LifeTableFemale <-  read.dbf(file = "/data/LIFETABLEFEMALE.DBF")

# import data as excel
mac <- TRUE # working on macbook = TRUE; change to FALSE when working on windows RDP

if (mac == FALSE) {
  str.path <- "~/data/"
} else {
  str.path <- "~/Box/Actuary/work-apps/AUW/proj/data/"
}

lifeTableMale <- read_xls(path = paste0(str.path, "lifetablemale.xls"), col_names = TRUE)
  lifeTableMale <- lifeTableMale[-1,] # clean; delete the first row to be consistent between male and female

lifeTableFemale <- read_xls(path = paste0(str.path, "lifetablefemale.xls"), col_names = TRUE)
medication <- read_xls(path = paste0(str.path, "medication.xls"), col_names = TRUE)
patients <- read_xls(path = paste0(str.path, "patients.xls"), col_names = TRUE)
patientsSurgery <- read_xls(path = paste0(str.path, "patientssurgery.xls"), col_names = TRUE)

Estimating (Remaining) Life Expectancy Per Patient

Question A) (10 points) For each patient, calculate their life expectancy if they do not take the medication from now on. Then calculate what their life expectancy would be if they take the medication for the rest of their life. For each patient, how much higher is the life expectancy without medication?

patients %>% qkable()

id	gender	dob	dom
2182	Female	1952-12-01	2014-10-01
1943	Male	1970-04-01	2015-05-01
1250	Female	1985-11-01	2012-05-01
2185	Male	1973-06-01	2013-04-01
1720	Female	1952-11-01	2015-11-01
1940	Male	1985-03-01	2015-06-01
2249	Female	1959-04-01	2012-09-01
1795	Female	1960-02-01	2015-10-01
1278	Male	1968-07-01	2015-09-01
2124	Male	1958-01-01	2012-08-01
2755	Female	1990-04-01	2012-12-01
1648	Female	1991-07-01	2012-07-01
2802	Female	1956-03-01	2016-05-01
2841	Female	1980-08-01	2015-11-01
2275	Female	1990-08-01	2012-06-01
2849	Male	1966-05-01	2015-04-01
1320	Female	1972-06-01	2014-07-01
2892	Male	1956-09-01	2012-07-01
1148	Male	1965-03-01	2014-04-01
2787	Male	1979-02-01	2012-01-01
2850	Male	1956-06-01	2016-09-01
1112	Male	1988-08-01	2014-08-01
1480	Male	1989-01-01	2016-12-01
1932	Male	1991-12-01	2015-12-01
2166	Female	1977-10-01	2013-03-01
1967	Male	1986-11-01	2015-11-01
1155	Female	1955-04-01	2016-05-01
1939	Male	1963-02-01	2016-06-01
1311	Female	1988-03-01	2014-07-01
2357	Female	1973-10-01	2013-05-01
1705	Female	1980-08-01	2016-07-01
1493	Male	1987-07-01	2012-11-01
2075	Male	1980-04-01	2013-02-01
2464	Male	1976-02-01	2015-02-01
1433	Male	1992-02-01	2016-11-01
2717	Male	1975-12-01	2013-10-01
1392	Male	1967-11-01	2012-10-01
1114	Female	1964-09-01	2014-05-01
1693	Female	1983-12-01	2016-12-01
1165	Female	1965-04-01	2012-06-01
1187	Female	1984-07-01	2016-10-01
1984	Female	1953-02-01	2016-04-01
1882	Female	1953-08-01	2016-11-01
1243	Female	1953-03-01	2013-09-01
1931	Female	1985-11-01	2012-07-01
2066	Male	1978-01-01	2012-02-01
1819	Male	1970-10-01	2015-07-01
2096	Female	1967-07-01	2013-08-01
2789	Female	1985-12-01	2012-06-01
2459	Male	1953-03-01	2016-02-01
1145	Male	1990-04-01	2013-07-01
2314	Female	1988-04-01	2013-03-01
1901	Male	1955-06-01	2012-12-01
1809	Female	1963-06-01	2016-02-01
2806	Male	1976-12-01	2014-10-01
2612	Female	1957-04-01	2016-01-01
1193	Male	1992-05-01	2016-11-01
2448	Female	1986-02-01	2015-07-01
1889	Female	1966-06-01	2016-07-01
2154	Male	1957-05-01	2014-08-01
2097	Male	1978-07-01	2016-05-01
2396	Female	1966-07-01	2012-03-01
2618	Male	1955-08-01	2015-02-01
2047	Male	1954-08-01	2014-12-01
2615	Male	1985-08-01	2013-02-01
2222	Male	1968-07-01	2015-04-01
2358	Female	1980-07-01	2016-09-01
2910	Female	1980-12-01	2012-07-01
1827	Female	1980-05-01	2013-01-01
2560	Male	1952-04-01	2014-12-01
2707	Male	1956-10-01	2015-09-01
2058	Male	1953-11-01	2015-07-01
1427	Female	1979-12-01	2016-11-01
2045	Male	1989-06-01	2013-12-01
2390	Male	1990-05-01	2012-08-01
2332	Male	1976-02-01	2013-12-01
1190	Male	1957-02-01	2016-04-01
1645	Female	1974-08-01	2016-12-01
2491	Female	1988-10-01	2012-09-01
2836	Female	1986-04-01	2012-04-01
2906	Male	1968-09-01	2014-10-01
1340	Female	1988-05-01	2016-12-01
1829	Male	1987-05-01	2015-11-01
2246	Female	1966-12-01	2012-09-01
1683	Male	1991-07-01	2012-05-01
1903	Female	1989-08-01	2016-05-01
2060	Male	1992-07-01	2015-04-01
1176	Male	1970-07-01	2016-09-01
2192	Male	1957-04-01	2012-05-01
1479	Female	1979-10-01	2013-07-01
2574	Female	1958-12-01	2013-05-01
1928	Female	1983-06-01	2014-07-01
1906	Female	1971-12-01	2012-10-01
2723	Male	1982-11-01	2012-10-01
2597	Female	1954-04-01	2013-04-01
2267	Male	1975-04-01	2015-09-01
2119	Female	1990-10-01	2016-10-01
1238	Female	1969-09-01	2015-03-01
1921	Female	1990-11-01	2015-03-01
2153	Female	1958-10-01	2015-11-01
1792	Female	1984-08-01	2016-10-01
1981	Male	1955-12-01	2016-06-01
1322	Female	1972-08-01	2014-12-01
1152	Female	1969-11-01	2012-05-01
2953	Male	1970-07-01	2016-11-01
2770	Male	1953-02-01	2013-12-01
1546	Male	1986-09-01	2012-02-01
2608	Female	1986-11-01	2015-02-01
1801	Male	1962-07-01	2012-08-01
1218	Female	1953-11-01	2016-11-01
2265	Female	1961-12-01	2014-11-01
1383	Female	1990-01-01	2013-11-01
1039	Female	1982-12-01	2015-11-01
2108	Male	1967-05-01	2015-05-01
1666	Female	1969-05-01	2013-09-01
1786	Male	1967-02-01	2016-02-01
2048	Female	1967-01-01	2013-12-01
2759	Female	1960-12-01	2012-01-01
2439	Male	1969-01-01	2013-05-01
1847	Female	1984-02-01	2013-10-01
1769	Female	1984-04-01	2014-02-01
2328	Female	1963-04-01	2014-01-01
1806	Male	1960-08-01	2015-03-01
1807	Female	1976-10-01	2015-05-01
2235	Male	1953-02-01	2012-03-01
1775	Female	1971-01-01	2012-03-01
1875	Male	1964-09-01	2014-12-01
1914	Female	1977-07-01	2013-03-01
2044	Male	1963-09-01	2016-09-01
1833	Male	1978-07-01	2012-07-01
1598	Male	1980-08-01	2015-06-01
2434	Female	1956-04-01	2014-12-01
2965	Male	1958-12-01	2014-11-01
1715	Male	1962-01-01	2015-07-01
1642	Male	1975-03-01	2013-07-01
1636	Female	1960-02-01	2015-12-01
2305	Female	1992-12-01	2014-02-01
1125	Female	1971-07-01	2014-02-01
2594	Female	1978-05-01	2014-04-01
2567	Male	1968-10-01	2013-02-01
1740	Female	1991-06-01	2013-12-01
1035	Female	1969-07-01	2012-10-01
1213	Female	1965-02-01	2014-05-01
2378	Male	1985-01-01	2012-05-01
1690	Female	1970-12-01	2013-07-01
1619	Male	1957-08-01	2015-06-01
2406	Male	1967-02-01	2013-11-01
2804	Male	1981-02-01	2012-11-01
2561	Male	1956-05-01	2016-08-01
1554	Male	1953-05-01	2012-12-01
1297	Female	1952-06-01	2013-11-01
1756	Male	1976-07-01	2013-08-01
2082	Female	1956-08-01	2016-04-01
1999	Male	1987-04-01	2012-06-01
1597	Male	1964-12-01	2012-11-01
1221	Male	1956-02-01	2016-02-01
1455	Male	1985-11-01	2016-02-01
1254	Male	1966-08-01	2012-03-01
2159	Female	1954-11-01	2016-07-01
2663	Male	1987-02-01	2016-05-01
2742	Male	1963-01-01	2016-10-01
2528	Female	1969-04-01	2015-08-01
2270	Male	1980-04-01	2015-04-01
2411	Female	1961-06-01	2016-07-01
2795	Male	1954-07-01	2012-06-01
2333	Male	1970-02-01	2014-11-01
1725	Female	1980-10-01	2015-04-01
2310	Female	1952-10-01	2016-11-01
2362	Male	1967-03-01	2014-08-01
1941	Male	1979-07-01	2014-04-01
1641	Male	1967-09-01	2015-12-01
1601	Female	1969-07-01	2016-12-01
1980	Female	1975-10-01	2012-01-01
1349	Female	1985-09-01	2016-05-01
1256	Female	1957-08-01	2016-12-01
2569	Male	1991-05-01	2012-10-01
2963	Male	1965-06-01	2014-01-01
2572	Female	1987-06-01	2015-07-01
1286	Male	1964-06-01	2014-04-01
1852	Male	1953-07-01	2013-01-01
2410	Male	1982-06-01	2015-09-01
2364	Female	1983-12-01	2013-12-01
2880	Female	1962-09-01	2014-08-01
1894	Female	1961-06-01	2013-11-01
1179	Male	1984-08-01	2012-07-01
2006	Male	1965-04-01	2013-02-01
1497	Female	1969-03-01	2012-06-01
2135	Male	1986-03-01	2013-02-01
1864	Male	1953-05-01	2012-08-01
1831	Male	1958-11-01	2014-11-01
2678	Male	1968-09-01	2014-10-01
2039	Female	1953-11-01	2015-02-01
1394	Male	1989-09-01	2016-11-01
2457	Male	1969-04-01	2014-08-01
1599	Female	1968-08-01	2012-05-01
2586	Male	1977-04-01	2013-04-01
2304	Male	1988-12-01	2014-08-01
2480	Male	1952-02-01	2014-10-01
1668	Female	1990-12-01	2016-04-01
2155	Male	1984-04-01	2015-04-01
1927	Female	1963-02-01	2013-05-01
1591	Male	1982-04-01	2016-08-01
2752	Male	1961-02-01	2012-06-01
2025	Male	1972-03-01	2015-04-01
1838	Female	1956-10-01	2013-04-01
2210	Female	1957-07-01	2014-06-01
2864	Male	1965-08-01	2014-01-01
2549	Male	1981-10-01	2014-02-01
2173	Female	1979-04-01	2013-09-01
1374	Male	1966-01-01	2016-09-01
2885	Male	1973-02-01	2012-12-01
1658	Male	1956-09-01	2013-06-01
1710	Female	1973-10-01	2016-03-01
2131	Female	1983-01-01	2016-05-01
1822	Female	1978-07-01	2014-06-01
1897	Male	1970-10-01	2012-07-01
1589	Female	1992-01-01	2015-01-01
2718	Female	1962-09-01	2015-09-01
2682	Female	1987-04-01	2012-02-01
2196	Male	1953-03-01	2015-06-01
2269	Male	1956-09-01	2014-09-01
2470	Female	1965-04-01	2012-03-01
2532	Male	1977-10-01	2012-09-01
1992	Male	1984-12-01	2014-02-01
1233	Female	1987-01-01	2013-05-01
1149	Female	1965-04-01	2013-11-01
1285	Female	1953-05-01	2016-06-01
1684	Male	1986-04-01	2014-04-01
1099	Female	1959-12-01	2012-11-01
2478	Male	1964-11-01	2012-08-01
2024	Male	1974-08-01	2012-03-01
2794	Female	1989-02-01	2014-09-01
1173	Male	1969-06-01	2013-01-01
1624	Female	1972-11-01	2015-01-01
2037	Female	1970-02-01	2015-02-01
2685	Male	1988-01-01	2013-12-01
2693	Female	1974-08-01	2013-02-01
2429	Female	1990-07-01	2012-10-01
2855	Male	1959-03-01	2014-09-01
2122	Female	1952-03-01	2015-05-01
1670	Male	1980-12-01	2014-09-01
2564	Male	1964-11-01	2013-06-01
1813	Female	1955-01-01	2015-03-01
1560	Male	1962-06-01	2015-08-01
1644	Male	1967-02-01	2015-06-01
2452	Male	1986-06-01	2012-11-01
2992	Female	1974-03-01	2015-11-01
2015	Male	1956-11-01	2014-12-01
2426	Female	1954-07-01	2014-11-01
1737	Female	1992-03-01	2013-02-01
2875	Male	1967-06-01	2014-07-01
1659	Female	1967-08-01	2016-01-01
1147	Female	1973-05-01	2013-03-01
1990	Male	1986-12-01	2012-07-01
1744	Male	1955-07-01	2012-08-01
2050	Female	1953-04-01	2016-11-01
2767	Male	1963-01-01	2013-12-01
1316	Male	1973-02-01	2014-12-01
1563	Male	1990-05-01	2012-12-01
2501	Female	1958-12-01	2012-07-01
1078	Male	1971-06-01	2016-09-01
1348	Male	1981-05-01	2016-10-01
1585	Male	1987-08-01	2015-01-01
2671	Male	1966-09-01	2016-04-01
1418	Male	1984-05-01	2015-11-01
1084	Female	1988-01-01	2014-08-01
1050	Male	1962-04-01	2015-01-01
1898	Male	1966-12-01	2016-01-01
2960	Female	1958-12-01	2012-09-01
1998	Female	1971-08-01	2016-12-01
2341	Female	1964-07-01	2012-06-01
2557	Female	1963-02-01	2016-01-01
2536	Female	1970-09-01	2013-05-01
1607	Male	1955-02-01	2013-12-01
2106	Female	1981-07-01	2014-04-01
2007	Female	1976-10-01	2013-06-01
2979	Female	1968-05-01	2014-05-01
1696	Male	1952-04-01	2013-07-01
1531	Female	1985-10-01	2014-10-01
1428	Male	1971-03-01	2013-03-01
2745	Male	1958-04-01	2015-05-01
1315	Female	1985-03-01	2013-12-01
1490	Female	1992-09-01	2012-04-01
1091	Male	1952-02-01	2015-05-01
2702	Male	1979-02-01	2015-01-01
2117	Male	1959-07-01	2012-06-01
1143	Male	1987-12-01	2012-05-01
2934	Female	1975-07-01	2013-10-01
1951	Female	1990-12-01	2015-03-01
1175	Female	1952-01-01	2015-12-01
2636	Male	1984-02-01	2015-11-01
2813	Male	1968-07-01	2016-03-01
1071	Female	1982-05-01	2015-09-01
2961	Female	1982-02-01	2014-08-01
2367	Female	1982-11-01	2013-05-01
2514	Male	1954-06-01	2013-09-01
2486	Female	1977-08-01	2013-05-01
1051	Male	1957-11-01	2012-01-01
1570	Male	1991-06-01	2012-03-01
1621	Female	1955-09-01	2016-10-01
2721	Female	1984-06-01	2014-05-01
1965	Male	1972-02-01	2015-12-01
1654	Female	1978-12-01	2012-12-01
2772	Male	1971-07-01	2013-08-01
1244	Male	1980-10-01	2016-06-01
2654	Female	1953-09-01	2013-07-01
2768	Female	1987-10-01	2013-11-01
2625	Female	1974-07-01	2015-09-01
1379	Male	1975-05-01	2014-05-01
2170	Male	1968-11-01	2016-04-01
2634	Female	1952-10-01	2013-06-01
1876	Female	1976-07-01	2013-03-01
1495	Female	1984-04-01	2016-06-01
1726	Male	1971-03-01	2015-06-01
2901	Male	1985-07-01	2013-07-01
2524	Female	1977-11-01	2012-12-01
1963	Female	1989-06-01	2012-10-01
1453	Male	1991-11-01	2012-08-01
2592	Male	1968-11-01	2012-10-01
1395	Male	1966-12-01	2016-12-01
1758	Male	1992-12-01	2014-12-01
2638	Male	1958-10-01	2014-09-01
1478	Male	1981-03-01	2013-07-01
1879	Male	1969-07-01	2015-09-01
2555	Male	1970-02-01	2013-03-01
1398	Male	1976-02-01	2014-10-01
1650	Female	1975-08-01	2015-05-01
1448	Female	1988-03-01	2015-09-01
2184	Female	1989-03-01	2014-05-01
1808	Male	1974-01-01	2013-05-01
1936	Male	1963-07-01	2016-10-01
1484	Female	1991-08-01	2012-08-01
2527	Female	1960-12-01	2012-03-01
1790	Female	1963-03-01	2012-10-01
2512	Male	1952-03-01	2015-01-01
1172	Male	1974-05-01	2012-03-01
2427	Female	1982-09-01	2012-06-01
1429	Male	1973-08-01	2012-01-01
2493	Female	1985-08-01	2013-09-01
1360	Male	1960-05-01	2012-08-01
1298	Male	1968-07-01	2016-07-01
1755	Male	1990-10-01	2012-12-01
2763	Male	1982-12-01	2013-10-01
2756	Female	1981-11-01	2012-02-01
1934	Male	1982-10-01	2014-08-01
2028	Female	1957-03-01	2012-10-01
2306	Female	1967-10-01	2012-07-01
2573	Female	1955-09-01	2016-02-01
2913	Female	1963-07-01	2012-07-01
2610	Male	1958-10-01	2015-01-01
2639	Female	1980-09-01	2012-09-01
2125	Male	1974-01-01	2012-04-01
1588	Male	1992-12-01	2014-10-01
2587	Female	1966-03-01	2012-12-01
1577	Female	1979-10-01	2013-01-01
1861	Female	1967-09-01	2015-10-01
2710	Male	1976-08-01	2015-05-01
1866	Female	1955-12-01	2016-07-01
1750	Male	1962-03-01	2015-02-01
1714	Female	1957-02-01	2013-10-01
2260	Female	1958-02-01	2014-04-01
1058	Female	1980-09-01	2016-08-01
1270	Female	1956-02-01	2016-01-01
2017	Male	1952-01-01	2015-12-01
2324	Female	1992-11-01	2012-09-01
2683	Female	1955-06-01	2013-02-01
2247	Male	1983-12-01	2016-11-01
1264	Female	1968-05-01	2014-03-01
1203	Male	1991-02-01	2012-03-01
1494	Male	1986-03-01	2015-05-01
2063	Female	1964-03-01	2013-10-01
1475	Male	1983-01-01	2013-01-01
2509	Male	1957-05-01	2016-02-01
2377	Male	1988-09-01	2013-07-01
2261	Female	1969-08-01	2015-06-01
2788	Male	1961-06-01	2012-01-01
1055	Female	1968-11-01	2013-06-01
1513	Male	1952-12-01	2013-11-01
1082	Female	1969-03-01	2016-12-01
1269	Female	1966-10-01	2014-04-01
2100	Male	1983-03-01	2016-03-01
1365	Male	1973-04-01	2013-11-01
1610	Male	1991-02-01	2013-05-01
2703	Male	1980-05-01	2015-12-01
1178	Female	1984-03-01	2015-07-01
1848	Female	1978-11-01	2013-11-01
2844	Female	1961-04-01	2012-05-01
2285	Male	1962-01-01	2013-12-01
1045	Female	1957-12-01	2014-11-01
1537	Female	1968-11-01	2014-12-01
1747	Female	1968-10-01	2012-02-01
1248	Male	1989-06-01	2016-07-01
1317	Male	1960-05-01	2016-02-01
2317	Female	1978-02-01	2012-05-01
2227	Male	1981-04-01	2016-10-01
2653	Male	1960-04-01	2015-03-01
2254	Female	1965-02-01	2016-03-01
2468	Female	1972-10-01	2014-06-01
1354	Female	1982-05-01	2013-12-01
1509	Female	1967-03-01	2012-04-01
1854	Male	1962-01-01	2013-10-01
1028	Female	1979-11-01	2016-07-01
2985	Male	1952-09-01	2016-12-01
2935	Female	1989-05-01	2014-12-01
1235	Female	1984-09-01	2014-02-01
2385	Male	1971-06-01	2014-09-01
2446	Female	1972-05-01	2015-06-01
1158	Male	1971-09-01	2013-06-01
2878	Female	1983-04-01	2012-05-01
2224	Male	1989-12-01	2015-05-01
2453	Male	1957-08-01	2012-10-01
2861	Female	1956-10-01	2012-09-01
2627	Female	1968-03-01	2016-12-01
2986	Female	1982-11-01	2014-09-01
1909	Male	1954-11-01	2015-03-01
2315	Female	1978-08-01	2013-06-01
2232	Male	1970-10-01	2013-04-01
1765	Male	1962-08-01	2013-05-01
1639	Male	1952-11-01	2016-04-01
1425	Female	1982-12-01	2012-04-01
1266	Male	1985-01-01	2014-10-01
1030	Male	1955-09-01	2016-06-01
1590	Female	1966-06-01	2012-10-01
1799	Male	1957-11-01	2016-11-01
1657	Male	1980-09-01	2013-06-01
2681	Male	1988-10-01	2014-07-01
2215	Female	1962-07-01	2015-12-01
2384	Male	1982-01-01	2013-03-01
2018	Female	1954-09-01	2013-11-01
1214	Male	1973-11-01	2013-07-01
1538	Male	1977-10-01	2013-07-01
1195	Female	1971-07-01	2016-06-01
2373	Male	1966-12-01	2014-12-01
1679	Male	1990-09-01	2014-09-01
1713	Male	1978-05-01	2015-08-01
2476	Male	1971-01-01	2012-03-01
2997	Female	1972-07-01	2015-12-01
1952	Male	1962-10-01	2012-02-01
1265	Male	1984-02-01	2015-10-01
2943	Female	1966-02-01	2015-11-01
2251	Male	1957-12-01	2016-12-01
2440	Male	1983-12-01	2014-03-01
2716	Male	1957-12-01	2013-02-01
2781	Female	1976-11-01	2013-03-01
1776	Male	1991-02-01	2012-09-01
1929	Male	1976-03-01	2014-02-01
2820	Female	1980-04-01	2014-05-01
1015	Female	1962-02-01	2015-09-01
2853	Male	1986-06-01	2012-01-01
2728	Female	1985-02-01	2012-08-01
2593	Male	1991-10-01	2015-12-01
1605	Male	1955-08-01	2013-01-01
2927	Male	1967-04-01	2013-12-01
2255	Male	1992-07-01	2015-09-01
1991	Female	1959-10-01	2014-01-01
1535	Male	1981-01-01	2014-10-01
1474	Male	1989-11-01	2016-03-01
1307	Male	1992-04-01	2013-09-01
2510	Female	1991-03-01	2013-03-01
1424	Female	1983-04-01	2014-11-01
1907	Male	1966-07-01	2013-04-01
1620	Female	1964-11-01	2012-03-01
2554	Male	1966-11-01	2016-09-01
1883	Male	1957-06-01	2013-09-01
2141	Female	1983-10-01	2012-08-01
1824	Male	1983-10-01	2013-04-01
1435	Male	1970-10-01	2015-10-01
2083	Male	1968-05-01	2012-01-01
1291	Female	1955-11-01	2014-05-01
1739	Female	1968-09-01	2013-10-01
2504	Female	1962-03-01	2016-07-01
2558	Male	1962-04-01	2014-02-01
1227	Female	1962-02-01	2016-10-01
2094	Male	1957-09-01	2015-01-01
1582	Female	1953-03-01	2015-05-01
1024	Male	1980-04-01	2014-02-01
2938	Female	1976-08-01	2012-11-01
1826	Female	1955-03-01	2016-03-01
2471	Female	1987-06-01	2014-05-01
1231	Male	1952-03-01	2013-04-01
2903	Male	1955-09-01	2013-07-01
1502	Male	1977-12-01	2015-07-01
1168	Female	1987-04-01	2015-04-01
1578	Male	1975-08-01	2016-07-01
1373	Female	1978-11-01	2012-11-01
1388	Female	1979-02-01	2013-03-01
1702	Male	1990-03-01	2015-03-01
2394	Female	1957-10-01	2015-04-01
1228	Female	1970-08-01	2016-02-01
1552	Female	1992-08-01	2015-08-01
2741	Female	1960-10-01	2014-03-01
1902	Male	1962-08-01	2012-03-01
1798	Female	1992-11-01	2012-04-01
2537	Male	1971-12-01	2016-07-01
2189	Male	1978-08-01	2015-09-01
2660	Male	1965-10-01	2014-10-01
2659	Female	1979-09-01	2016-12-01
1604	Female	1965-01-01	2013-03-01
1498	Female	1964-06-01	2016-09-01
2188	Male	1988-11-01	2015-06-01
2508	Male	1960-11-01	2012-08-01
2673	Male	1979-10-01	2016-03-01
2374	Male	1963-07-01	2012-09-01
2456	Male	1988-04-01	2016-03-01
1282	Male	1979-05-01	2014-09-01
2640	Male	1958-05-01	2014-04-01
1553	Male	1969-09-01	2012-05-01
1432	Female	1977-03-01	2013-06-01
2409	Female	1965-05-01	2015-07-01
2957	Male	1976-04-01	2016-02-01
1540	Male	1992-02-01	2015-10-01
1918	Male	1967-03-01	2012-06-01
2891	Female	1983-04-01	2014-07-01
1068	Male	1959-02-01	2012-03-01
1019	Female	1970-11-01	2014-06-01
1817	Female	1977-11-01	2012-03-01
1510	Female	1961-01-01	2013-10-01
2400	Male	1979-10-01	2015-10-01
2035	Male	1964-05-01	2016-01-01
2174	Male	1983-03-01	2015-08-01
2936	Female	1952-03-01	2013-10-01
2605	Male	1973-12-01	2015-04-01
1236	Male	1967-07-01	2012-01-01
2857	Female	1970-02-01	2016-04-01
2167	Male	1961-05-01	2016-07-01
1925	Male	1955-03-01	2015-08-01
2764	Male	1957-09-01	2015-04-01
2449	Female	1961-11-01	2013-10-01
2068	Male	1991-07-01	2012-01-01
1314	Female	1965-12-01	2012-12-01
2259	Female	1978-08-01	2012-06-01
2733	Female	1972-12-01	2014-12-01
1964	Female	1969-02-01	2013-09-01
1159	Female	1961-12-01	2015-02-01
2284	Female	1953-04-01	2015-04-01
2999	Male	1960-01-01	2016-05-01
1111	Male	1962-01-01	2015-04-01
1072	Female	1959-07-01	2013-07-01
1579	Female	1978-04-01	2012-11-01
2466	Male	1968-08-01	2015-07-01
1986	Male	1979-08-01	2016-04-01
2110	Male	1955-04-01	2012-04-01
1692	Female	1967-10-01	2013-01-01
1366	Female	1958-05-01	2013-12-01
2376	Male	1955-11-01	2012-01-01
1280	Female	1963-10-01	2014-08-01
2383	Male	1960-02-01	2016-08-01
1309	Female	1965-06-01	2015-05-01
1997	Male	1964-09-01	2012-12-01
2250	Male	1982-05-01	2013-06-01
2299	Female	1966-02-01	2013-12-01
1682	Female	1990-06-01	2013-01-01
2919	Male	1976-05-01	2015-04-01
1637	Female	1992-03-01	2012-02-01
1564	Male	1991-08-01	2012-07-01
1862	Female	1956-08-01	2016-07-01
2983	Female	1954-07-01	2014-03-01
1488	Female	1987-12-01	2014-01-01
2162	Male	1957-10-01	2012-03-01
2145	Male	1966-12-01	2015-01-01
1743	Female	1990-01-01	2016-10-01
2391	Male	1953-05-01	2013-11-01
1160	Male	1954-10-01	2015-11-01
2126	Male	1954-05-01	2016-09-01
2931	Male	1987-10-01	2016-06-01
1757	Female	1988-11-01	2015-06-01
2915	Male	1963-12-01	2016-06-01
2541	Female	1964-02-01	2012-09-01
1905	Female	1976-10-01	2016-04-01
2276	Female	1964-11-01	2015-09-01
1830	Female	1976-03-01	2016-03-01
2545	Male	1969-02-01	2012-07-01
1622	Male	1988-05-01	2012-06-01
2542	Male	1958-07-01	2015-04-01
2958	Male	1988-11-01	2013-06-01
2817	Male	1987-10-01	2014-01-01
1463	Male	1990-09-01	2016-03-01
2405	Female	1954-07-01	2012-03-01
2652	Female	1992-12-01	2012-09-01
2152	Male	1966-05-01	2012-04-01
1763	Male	1978-03-01	2013-03-01
2604	Male	1964-04-01	2014-12-01
2730	Male	1952-02-01	2015-07-01
1718	Female	1955-03-01	2016-08-01
2228	Male	1991-01-01	2015-02-01
2325	Female	1982-05-01	2014-05-01
1561	Male	1982-06-01	2016-09-01
1640	Male	1952-12-01	2012-09-01
2899	Male	1973-06-01	2015-01-01
2401	Male	1961-07-01	2013-01-01
2005	Male	1980-02-01	2012-12-01
1749	Male	1963-09-01	2016-08-01
1027	Female	1991-05-01	2014-01-01
1452	Female	1972-03-01	2012-10-01
2206	Female	1957-05-01	2015-04-01
2339	Male	1974-05-01	2013-04-01
2658	Male	1959-11-01	2015-05-01
2272	Male	1979-10-01	2013-01-01
1523	Female	1991-05-01	2013-10-01
2622	Male	1987-09-01	2013-12-01

First we’ll calculate the (floor of) age of each patient.

For simplicity (although we can probably do better), we’ll construct a life expectancy table with the following columns:

• age
• female_no_med: female life expectancy, not taking this medication for rest of life
• female_med: female life expectancy, taking medication each month for rest of life
• male_no_med: male life expectancy, not taking this medication for rest of life
• male_med: female life expectancy, taking medication each month for rest of life

We’ll follow the definition of ’life expectancy" as given by the Social Security Administration and apply it to our problem

"the period life expectancy at a given age is the average remaining number of years expected prior to death for [the exact] person, using the mortality rates from the given table.

td <- as_date("2018-01-01") # given today's date is January 1, 2018

 # fix date of birth and date of medication into date format via lubridate library
for ( col in c("dob", "dom")) {
  patients[[col]] <- as_date(pull(patients, col))
}

# fix date of surgery
patientsSurgery[["dos"]] <- as_date(pull(patientsSurgery, "dos"))


# get age (truncated / rounded down)
patients <- patients %>% 
  mutate(age = interval(pull(patients, "dob"), td) %/% years(1))

Let’s check seasonality of the birthday months (and similarly for the distribution of birth year).

# birth month
month.abb <- month.abb %>% factor(levels = month.abb)
tmp <- month.abb[month(patients$dob)] %>% factor(levels = month.abb)

select(patients, id, gender, dob) %>% 
  mutate(bday.month = tmp ) %>%
  ggplot(aes(x = bday.month)) + geom_bar(color = 'white', aes(fill = gender)) + ggtitle("Distribution/Seasonality of Birth Month") + facet_grid(gender ~ .)

# birth year
n.years <- max(year(patients$dob)) - min(year(patients$dob)) + 1 # for bin size; most refined granularity 

select(patients, id, gender, dob) %>% 
  mutate(bday.year = year(dob)) %>%
  ggplot(aes(x = bday.year)) + geom_histogram(bins = n.years, color = 'white', aes(fill = gender)) + ggtitle("Distribution of Birth Year") + facet_grid(gender ~ .)

Now we’ll calculate the life expectancies. We’ll use these life expectancies as a “lookup table” based on current age for each patient in patients. To construct the tables, we could manually code up a function with lead and lag to emulate excel functions, but for this time we’ll use an existing function via the MortalityLaws package in R.

require(MortalityLaws)

female <- LifeTable( x = lifeTableFemale$age, qx = lifeTableFemale$prob )
female.med <- LifeTable( x = lifeTableFemale$age, qx = lifeTableFemale$medprob )
male <- LifeTable( x = lifeTableMale$age, qx = lifeTableMale$prob )
male.med <- LifeTable( x = lifeTableMale$age, qx = lifeTableMale$medprob )

LifeExp <- tibble(age = as.integer(female$lt$x), # x : age in LifeTable
                     female_life_exp = female$lt$ex, # ex : life expectancy
                     female_life_exp_med = female.med$lt$ex,
                     male_life_exp = male$lt$ex,
                     male_life_exp_med = male.med$lt$ex) %>%
  mutate(female_diff = female_life_exp - female_life_exp_med,
         male_diff = male_life_exp - male_life_exp_med)

LifeExp %>% qkable()

age	female_life_exp	female_life_exp_med	male_life_exp	male_life_exp_med	female_diff	male_diff
0	80.9393387	49.2526956	76.1779593	47.3724449	31.6866432	28.8055144
1	80.3852617	49.1723079	75.6783786	47.3080325	31.2129539	28.3703461
2	79.4157896	48.8324903	74.7117727	46.9459105	30.5832993	27.7658622
3	78.4329970	48.4799215	73.7333746	46.5715533	29.9530754	27.1618213
4	77.4459360	48.1200679	72.7499291	46.1890777	29.3258681	26.5608513
5	76.4569408	47.7542885	71.7624305	45.7989889	28.7026524	25.9634416
6	75.4665886	47.3828613	70.7736917	45.4029950	28.0837273	25.3706967
7	74.4752857	47.0059639	69.7840235	45.0012140	27.4693218	24.7828095
8	73.4831280	46.6235800	68.7934474	44.5935777	26.8595480	24.1998697
9	72.4902810	46.2357391	67.8017119	44.1798363	26.2545419	23.6218756
10	71.4968327	45.8424221	66.8087120	43.7598315	25.6544106	23.0488805
11	70.5029390	45.4436551	65.8148130	43.3337169	25.0592838	22.4810961
12	69.5091698	45.0397391	64.8211491	42.9021695	24.4694306	21.9189796
13	68.5162094	44.6310607	63.8297693	42.4665061	23.8851487	21.3632633
14	67.5249166	44.2181348	62.8430714	42.0283198	23.3067818	20.8147516
15	66.5359105	43.8013321	61.8628404	41.5888316	22.7345784	20.2740087
16	65.5494507	43.3807926	60.8894220	41.1483085	22.1686581	19.7411135
17	64.5654568	42.9564312	59.9224731	40.7065490	21.6090256	19.2159241
18	63.5837206	42.5280740	58.9624319	40.2638886	21.0556467	18.6985433
19	62.6038508	42.0954172	58.0095322	39.8205295	20.5084336	18.1890027
20	61.6255947	41.6582447	57.0636997	39.3764636	19.9673500	17.6872361
21	60.6490148	41.2165524	56.1250814	38.9318382	19.4324625	17.1932431
22	59.6741074	40.7702910	55.1929712	38.4862000	18.9038165	16.7067712
23	58.7005109	40.3191606	54.2649325	38.0378479	18.3813503	16.2270845
24	57.7278198	39.8628227	53.3379908	37.5846465	17.8649971	15.7533443
25	56.7557033	39.4009825	52.4100544	37.1250346	17.3547208	15.2850199
26	55.7841268	38.9335478	51.4803841	36.6583862	16.8505790	14.8219979
27	54.8131662	38.4605040	50.5493008	36.1848308	16.3526622	14.3644700
28	53.8431093	37.9819888	49.6171094	35.7044911	15.8611205	13.9126182
29	52.8743333	37.4982100	48.6845415	35.2178091	15.3761233	13.4667324
30	51.9071402	37.0093277	47.7520463	34.7250318	14.8978126	13.0270145
31	50.9417091	36.5154182	46.8198084	34.2262180	14.4262909	12.5935904
32	49.9780533	36.0164395	45.8877202	33.7212112	13.9616138	12.1665091
33	49.0159826	35.5121993	44.9557677	33.2099215	13.5037833	11.7458462
34	48.0551181	35.0023609	44.0238895	32.6922223	13.0527572	11.3316672
35	47.0953839	34.4868043	43.0922423	32.1681497	12.6085796	10.9240926
36	46.1369374	33.9655819	42.1612257	31.6379277	12.1713555	10.5232980
37	45.1801509	33.4389103	41.2314182	31.1019253	11.7412407	10.1294929
38	44.2254579	32.9070599	40.3031913	30.5603635	11.3183980	9.7428278
39	43.2733461	32.3703513	39.3770438	30.0135695	10.9029948	9.3634743
40	42.3243071	31.8291184	38.4535099	29.4619083	10.4951887	8.9916016
41	41.3785819	31.2835126	37.5330755	28.9057186	10.0950693	8.6273569
42	40.4364252	30.7337014	36.6164394	28.3455168	9.7027238	8.2709226
43	39.4983829	30.1800837	35.7046344	27.7820993	9.3182992	7.9225350
44	38.5650281	29.6230920	34.7988476	27.2164084	8.9419360	7.5824392
45	37.6367636	29.0630382	33.9000533	26.6492429	8.5737254	7.2508103
46	36.7137593	28.5000592	33.0087566	26.0810477	8.2137001	6.9277089
47	35.7961520	27.9342701	32.1252625	25.5121175	7.8618819	6.6131449
48	34.8841174	27.3658203	31.2499139	24.9427862	7.5182971	6.3071277
49	33.9777596	26.7948062	30.3829903	24.3733441	7.1829534	6.0096461
50	33.0771487	26.2212995	29.5246786	23.8040133	6.8558491	5.7206653
51	32.1825836	25.6455595	28.6751077	23.2349732	6.5370241	5.4401344
52	31.2940227	25.0675752	27.8344063	22.6664072	6.2264474	5.1679991
53	30.4108359	24.4868533	27.0027538	22.0985448	5.9239826	4.9042091
54	29.5321672	23.9026997	26.1803419	21.5316310	5.6294675	4.6487109
55	28.6575755	23.3147438	25.3671589	20.9657457	5.3428317	4.4014131
56	27.7874733	22.7233143	24.5637642	20.4014522	5.0641590	4.1623120
57	26.9223935	22.1288503	23.7698944	19.8386293	4.7935432	3.9312651
58	26.0622728	21.5313021	22.9839814	19.2760373	4.5309706	3.7079440
59	25.2070059	20.9305831	22.2039909	18.7120010	4.2764228	3.4919898
60	24.3568460	20.3269080	21.4288360	18.1456131	4.0299380	3.2832230
61	23.5124260	19.7208213	20.6588854	17.5772001	3.7916047	3.0816853
62	22.6746760	19.1131373	19.8952985	17.0077850	3.5615387	2.8875135
63	21.8445441	18.5047081	19.1388926	16.4381185	3.3398360	2.7007741
64	21.0230621	17.8964809	18.3906251	15.8690934	3.1265812	2.5215317
65	20.2111678	17.2893463	17.6514792	15.3016478	2.9218215	2.3498313
66	19.4100779	16.6844617	16.9224712	14.7367723	2.7256162	2.1856989
67	18.6205092	16.0825750	16.2044325	14.1753170	2.5379342	2.0291156
68	17.8424096	15.4837729	15.4979920	13.6179703	2.3586367	1.8800217
69	17.0754930	14.8879337	14.8036236	13.0652967	2.1875593	1.7383270
70	16.3198868	14.2952975	14.1219243	12.5179823	2.0245894	1.6039420
71	15.5769955	13.7072509	13.4546512	11.9777790	1.8697446	1.4768722
72	14.8480999	13.1251034	12.8027351	11.4457279	1.7229965	1.3570072
73	14.1332759	12.5491061	12.1653500	10.9212864	1.5841698	1.2440636
74	13.4324016	11.9793320	11.5411650	10.4034281	1.4530696	1.1377369
75	12.7458183	11.4162728	10.9298434	9.8920099	1.3295455	1.0378334
76	12.0756476	10.8620575	10.3329465	9.3886348	1.2135901	0.9443117
77	11.4233938	10.3182975	9.7523911	8.8952699	1.1050963	0.8571212
78	10.7884491	9.7846879	9.1889546	8.4128616	1.0037612	0.7760930
79	10.1697337	9.2604696	8.6432561	7.9422230	0.9092641	0.7010331
80	9.5672088	8.7458244	8.1160526	7.4843075	0.8213844	0.6317451
81	8.9829931	8.2429456	7.6094320	7.0413372	0.7400475	0.5680948
82	8.4193162	7.7541756	7.1245510	6.6146974	0.6651407	0.5098536
83	7.8767493	7.2803417	6.6604895	6.2038244	0.5964076	0.4566652
84	7.3555415	6.8219744	6.2157280	5.8075577	0.5335671	0.4081703
85	6.8563095	6.3799515	5.7896619	5.4255711	0.4763580	0.3640908
86	6.3800675	5.9555348	5.3828360	5.0586177	0.4245326	0.3242183
87	5.9280330	5.5501940	4.9964832	4.7081140	0.3778389	0.2883691
88	5.5013264	5.1653229	4.6320084	4.3756582	0.3360035	0.2563503
89	5.1008192	4.8020903	4.2906104	4.0626673	0.2987289	0.2279431
90	4.7270136	4.4613181	3.9730095	3.7701092	0.2656954	0.2029003
91	4.3801119	4.1435390	3.6795398	3.4985802	0.2365729	0.1809596
92	4.0601282	3.8490980	3.4102900	3.2484339	0.2110303	0.1618561
93	3.7670474	3.5783036	3.1653179	3.0199864	0.1887438	0.1453316
94	3.5010479	3.3316479	2.9449692	2.8138302	0.1693999	0.1311390
95	3.2628085	3.1101178	2.7503097	2.6312744	0.1526907	0.1190353
96	3.0497668	2.9115602	2.5789168	2.4702420	0.1382065	0.1086747
97	2.8589094	2.7333503	2.4279712	2.3282440	0.1255591	0.0997272
98	2.6865080	2.5721285	2.2938930	2.2020211	0.1143795	0.0918719
99	2.5276210	2.4232971	2.1717188	2.0869166	0.1043239	0.0848023
100	2.3752968	2.2801970	2.0539990	1.9757410	0.0950998	0.0782580
101	2.2293025	2.1426542	1.9405748	1.8683694	0.0866484	0.0722053
102	2.0894053	2.0104911	1.8312918	1.7646793	0.0789141	0.0666125
103	1.9553669	1.8835218	1.7259904	1.6645407	0.0718451	0.0614496
104	1.8269539	1.7615611	1.6245132	1.5678244	0.0653928	0.0566888
105	1.7039248	1.6444130	1.5266953	1.4743914	0.0595117	0.0523039
106	1.5860336	1.5318736	1.4323761	1.3841051	0.0541600	0.0482710
107	1.4730327	1.4237337	1.3413864	1.2968186	0.0492989	0.0445678
108	1.3646668	1.3197735	1.2535527	1.2123784	0.0448933	0.0411743
109	1.2606664	1.2197549	1.1686929	1.1306204	0.0409116	0.0380725
110	1.1607537	1.1234274	1.0866135	1.0513664	0.0373264	0.0352471
111	1.0646272	1.0305115	1.0071039	0.9744186	0.0341157	0.0326853
112	0.9719731	0.9407080	0.9299293	0.8995511	0.0312652	0.0303782
113	0.8824769	0.8537039	0.8548121	0.8264911	0.0287730	0.0283210
114	0.7959481	0.7692883	0.7814198	0.7549055	0.0266597	0.0265143
115	0.7129092	0.6879346	0.7092992	0.6843382	0.0249746	0.0249610
116	0.6377139	0.6140724	0.6377139	0.6140724	0.0236415	0.0236415
117	0.5649450	0.5425860	0.5649450	0.5425860	0.0223590	0.0223590
118	0.4839447	0.4641874	0.4839447	0.4641874	0.0197573	0.0197573
119	0.3452017	0.3384444	0.3452017	0.3384444	0.0067573	0.0067573

Now let’s match up each patient with his/her corresponding life expectancy.

# fractional part of age
# fracpart <- interval(pull(patients, "dob"), td) / years(1) - 
#   (interval(pull(patients, "dob"), td) %/% years(1)) 

# we're looking at REMAINING life expectancy from today 1/1/2018, so we don't need to add this fractional part


patients <- patients %>% mutate(
  life_exp =  ifelse(gender == 'Female', 
                              LifeExp$female_life_exp[age - 1], 
                              LifeExp$male_life_exp[age - 1]),
  life_exp_med =  ifelse(gender == 'Female',
                                 LifeExp$female_life_exp_med[age - 1],
                                 LifeExp$male_life_exp_med[age - 1]),
  diff_exp = life_exp - life_exp_med
  )

Difference in life expectancy (diff_exp):

The remaining life expectancy (life_exp), life expectancy for those taking medications for the rest of their lives (life_exp_med), and the difference in these two life expectancies (diff_exp) for all patients by id are shown in the following table. (And better, visualized in the graph).

patients %>% qkable()

id	gender	dob	dom	age	life_exp	life_exp_med	diff_exp
2182	Female	1952-12-01	2014-10-01	65	21.84454	18.50471	3.339836
1943	Male	1970-04-01	2015-05-01	47	33.90005	26.64924	7.250810
1250	Female	1985-11-01	2012-05-01	32	51.90714	37.00933	14.897813
2185	Male	1973-06-01	2013-04-01	44	36.61644	28.34552	8.270923
1720	Female	1952-11-01	2015-11-01	65	21.84454	18.50471	3.339836
1940	Male	1985-03-01	2015-06-01	32	47.75205	34.72503	13.027014
2249	Female	1959-04-01	2012-09-01	58	27.78747	22.72331	5.064159
1795	Female	1960-02-01	2015-10-01	57	28.65758	23.31474	5.342832
1278	Male	1968-07-01	2015-09-01	49	32.12526	25.51212	6.613145
2124	Male	1958-01-01	2012-08-01	60	22.98398	19.27604	3.707944
2755	Female	1990-04-01	2012-12-01	27	56.75570	39.40098	17.354721
1648	Female	1991-07-01	2012-07-01	26	57.72782	39.86282	17.864997
2802	Female	1956-03-01	2016-05-01	61	25.20701	20.93058	4.276423
2841	Female	1980-08-01	2015-11-01	37	47.09538	34.48680	12.608580
2275	Female	1990-08-01	2012-06-01	27	56.75570	39.40098	17.354721
2849	Male	1966-05-01	2015-04-01	51	30.38299	24.37334	6.009646
1320	Female	1972-06-01	2014-07-01	45	39.49838	30.18008	9.318299
2892	Male	1956-09-01	2012-07-01	61	22.20399	18.71200	3.491990
1148	Male	1965-03-01	2014-04-01	52	29.52468	23.80401	5.720665
2787	Male	1979-02-01	2012-01-01	38	42.16123	31.63793	10.523298
2850	Male	1956-06-01	2016-09-01	61	22.20399	18.71200	3.491990
1112	Male	1988-08-01	2014-08-01	29	50.54930	36.18483	14.364470
1480	Male	1989-01-01	2016-12-01	29	50.54930	36.18483	14.364470
1932	Male	1991-12-01	2015-12-01	26	53.33799	37.58465	15.753344
2166	Female	1977-10-01	2013-03-01	40	44.22546	32.90706	11.318398
1967	Male	1986-11-01	2015-11-01	31	48.68454	35.21781	13.466732
1155	Female	1955-04-01	2016-05-01	62	24.35685	20.32691	4.029938
1939	Male	1963-02-01	2016-06-01	54	27.83441	22.66641	5.167999
1311	Female	1988-03-01	2014-07-01	29	54.81317	38.46050	16.352662
2357	Female	1973-10-01	2013-05-01	44	40.43643	30.73370	9.702724
1705	Female	1980-08-01	2016-07-01	37	47.09538	34.48680	12.608580
1493	Male	1987-07-01	2012-11-01	30	49.61711	35.70449	13.912618
2075	Male	1980-04-01	2013-02-01	37	43.09224	32.16815	10.924093
2464	Male	1976-02-01	2015-02-01	41	39.37704	30.01357	9.363474
1433	Male	1992-02-01	2016-11-01	25	54.26493	38.03785	16.227085
2717	Male	1975-12-01	2013-10-01	42	38.45351	29.46191	8.991602
1392	Male	1967-11-01	2012-10-01	50	31.24991	24.94279	6.307128
1114	Female	1964-09-01	2014-05-01	53	32.18258	25.64556	6.537024
1693	Female	1983-12-01	2016-12-01	34	49.97805	36.01644	13.961614
1165	Female	1965-04-01	2012-06-01	52	33.07715	26.22130	6.855849
1187	Female	1984-07-01	2016-10-01	33	50.94171	36.51542	14.426291
1984	Female	1953-02-01	2016-04-01	64	22.67468	19.11314	3.561539
1882	Female	1953-08-01	2016-11-01	64	22.67468	19.11314	3.561539
1243	Female	1953-03-01	2013-09-01	64	22.67468	19.11314	3.561539
1931	Female	1985-11-01	2012-07-01	32	51.90714	37.00933	14.897813
2066	Male	1978-01-01	2012-02-01	40	40.30319	30.56036	9.742828
1819	Male	1970-10-01	2015-07-01	47	33.90005	26.64924	7.250810
2096	Female	1967-07-01	2013-08-01	50	34.88412	27.36582	7.518297
2789	Female	1985-12-01	2012-06-01	32	51.90714	37.00933	14.897813
2459	Male	1953-03-01	2016-02-01	64	19.89530	17.00778	2.887513
1145	Male	1990-04-01	2013-07-01	27	52.41005	37.12503	15.285020
2314	Female	1988-04-01	2013-03-01	29	54.81317	38.46050	16.352662
1901	Male	1955-06-01	2012-12-01	62	21.42884	18.14561	3.283223
1809	Female	1963-06-01	2016-02-01	54	31.29402	25.06758	6.226447
2806	Male	1976-12-01	2014-10-01	41	39.37704	30.01357	9.363474
2612	Female	1957-04-01	2016-01-01	60	26.06227	21.53130	4.530971
1193	Male	1992-05-01	2016-11-01	25	54.26493	38.03785	16.227085
2448	Female	1986-02-01	2015-07-01	31	52.87433	37.49821	15.376123
1889	Female	1966-06-01	2016-07-01	51	33.97776	26.79481	7.182953
2154	Male	1957-05-01	2014-08-01	60	22.98398	19.27604	3.707944
2097	Male	1978-07-01	2016-05-01	39	41.23142	31.10193	10.129493
2396	Female	1966-07-01	2012-03-01	51	33.97776	26.79481	7.182953
2618	Male	1955-08-01	2015-02-01	62	21.42884	18.14561	3.283223
2047	Male	1954-08-01	2014-12-01	63	20.65889	17.57720	3.081685
2615	Male	1985-08-01	2013-02-01	32	47.75205	34.72503	13.027014
2222	Male	1968-07-01	2015-04-01	49	32.12526	25.51212	6.613145
2358	Female	1980-07-01	2016-09-01	37	47.09538	34.48680	12.608580
2910	Female	1980-12-01	2012-07-01	37	47.09538	34.48680	12.608580
1827	Female	1980-05-01	2013-01-01	37	47.09538	34.48680	12.608580
2560	Male	1952-04-01	2014-12-01	65	19.13889	16.43812	2.700774
2707	Male	1956-10-01	2015-09-01	61	22.20399	18.71200	3.491990
2058	Male	1953-11-01	2015-07-01	64	19.89530	17.00778	2.887513
1427	Female	1979-12-01	2016-11-01	38	46.13694	33.96558	12.171356
2045	Male	1989-06-01	2013-12-01	28	51.48038	36.65839	14.821998
2390	Male	1990-05-01	2012-08-01	27	52.41005	37.12503	15.285020
2332	Male	1976-02-01	2013-12-01	41	39.37704	30.01357	9.363474
1190	Male	1957-02-01	2016-04-01	60	22.98398	19.27604	3.707944
1645	Female	1974-08-01	2016-12-01	43	41.37858	31.28351	10.095069
2491	Female	1988-10-01	2012-09-01	29	54.81317	38.46050	16.352662
2836	Female	1986-04-01	2012-04-01	31	52.87433	37.49821	15.376123
2906	Male	1968-09-01	2014-10-01	49	32.12526	25.51212	6.613145
1340	Female	1988-05-01	2016-12-01	29	54.81317	38.46050	16.352662
1829	Male	1987-05-01	2015-11-01	30	49.61711	35.70449	13.912618
2246	Female	1966-12-01	2012-09-01	51	33.97776	26.79481	7.182953
1683	Male	1991-07-01	2012-05-01	26	53.33799	37.58465	15.753344
1903	Female	1989-08-01	2016-05-01	28	55.78413	38.93355	16.850579
2060	Male	1992-07-01	2015-04-01	25	54.26493	38.03785	16.227085
1176	Male	1970-07-01	2016-09-01	47	33.90005	26.64924	7.250810
2192	Male	1957-04-01	2012-05-01	60	22.98398	19.27604	3.707944
1479	Female	1979-10-01	2013-07-01	38	46.13694	33.96558	12.171356
2574	Female	1958-12-01	2013-05-01	59	26.92239	22.12885	4.793543
1928	Female	1983-06-01	2014-07-01	34	49.97805	36.01644	13.961614
1906	Female	1971-12-01	2012-10-01	46	38.56503	29.62309	8.941936
2723	Male	1982-11-01	2012-10-01	35	44.95577	33.20992	11.745846
2597	Female	1954-04-01	2013-04-01	63	23.51243	19.72082	3.791605
2267	Male	1975-04-01	2015-09-01	42	38.45351	29.46191	8.991602
2119	Female	1990-10-01	2016-10-01	27	56.75570	39.40098	17.354721
1238	Female	1969-09-01	2015-03-01	48	36.71376	28.50006	8.213700
1921	Female	1990-11-01	2015-03-01	27	56.75570	39.40098	17.354721
2153	Female	1958-10-01	2015-11-01	59	26.92239	22.12885	4.793543
1792	Female	1984-08-01	2016-10-01	33	50.94171	36.51542	14.426291
1981	Male	1955-12-01	2016-06-01	62	21.42884	18.14561	3.283223
1322	Female	1972-08-01	2014-12-01	45	39.49838	30.18008	9.318299
1152	Female	1969-11-01	2012-05-01	48	36.71376	28.50006	8.213700
2953	Male	1970-07-01	2016-11-01	47	33.90005	26.64924	7.250810
2770	Male	1953-02-01	2013-12-01	64	19.89530	17.00778	2.887513
1546	Male	1986-09-01	2012-02-01	31	48.68454	35.21781	13.466732
2608	Female	1986-11-01	2015-02-01	31	52.87433	37.49821	15.376123
1801	Male	1962-07-01	2012-08-01	55	27.00275	22.09854	4.904209
1218	Female	1953-11-01	2016-11-01	64	22.67468	19.11314	3.561539
2265	Female	1961-12-01	2014-11-01	56	29.53217	23.90270	5.629467
1383	Female	1990-01-01	2013-11-01	28	55.78413	38.93355	16.850579
1039	Female	1982-12-01	2015-11-01	35	49.01598	35.51220	13.503783
2108	Male	1967-05-01	2015-05-01	50	31.24991	24.94279	6.307128
1666	Female	1969-05-01	2013-09-01	48	36.71376	28.50006	8.213700
1786	Male	1967-02-01	2016-02-01	50	31.24991	24.94279	6.307128
2048	Female	1967-01-01	2013-12-01	51	33.97776	26.79481	7.182953
2759	Female	1960-12-01	2012-01-01	57	28.65758	23.31474	5.342832
2439	Male	1969-01-01	2013-05-01	49	32.12526	25.51212	6.613145
1847	Female	1984-02-01	2013-10-01	33	50.94171	36.51542	14.426291
1769	Female	1984-04-01	2014-02-01	33	50.94171	36.51542	14.426291
2328	Female	1963-04-01	2014-01-01	54	31.29402	25.06758	6.226447
1806	Male	1960-08-01	2015-03-01	57	25.36716	20.96575	4.401413
1807	Female	1976-10-01	2015-05-01	41	43.27335	32.37035	10.902995
2235	Male	1953-02-01	2012-03-01	64	19.89530	17.00778	2.887513
1775	Female	1971-01-01	2012-03-01	47	37.63676	29.06304	8.573725
1875	Male	1964-09-01	2014-12-01	53	28.67511	23.23497	5.440134
1914	Female	1977-07-01	2013-03-01	40	44.22546	32.90706	11.318398
2044	Male	1963-09-01	2016-09-01	54	27.83441	22.66641	5.167999
1833	Male	1978-07-01	2012-07-01	39	41.23142	31.10193	10.129493
1598	Male	1980-08-01	2015-06-01	37	43.09224	32.16815	10.924093
2434	Female	1956-04-01	2014-12-01	61	25.20701	20.93058	4.276423
2965	Male	1958-12-01	2014-11-01	59	23.76989	19.83863	3.931265
1715	Male	1962-01-01	2015-07-01	56	26.18034	21.53163	4.648711
1642	Male	1975-03-01	2013-07-01	42	38.45351	29.46191	8.991602
1636	Female	1960-02-01	2015-12-01	57	28.65758	23.31474	5.342832
2305	Female	1992-12-01	2014-02-01	25	58.70051	40.31916	18.381350
1125	Female	1971-07-01	2014-02-01	46	38.56503	29.62309	8.941936
2594	Female	1978-05-01	2014-04-01	39	45.18015	33.43891	11.741241
2567	Male	1968-10-01	2013-02-01	49	32.12526	25.51212	6.613145
1740	Female	1991-06-01	2013-12-01	26	57.72782	39.86282	17.864997
1035	Female	1969-07-01	2012-10-01	48	36.71376	28.50006	8.213700
1213	Female	1965-02-01	2014-05-01	52	33.07715	26.22130	6.855849
2378	Male	1985-01-01	2012-05-01	33	46.81981	34.22622	12.593590
1690	Female	1970-12-01	2013-07-01	47	37.63676	29.06304	8.573725
1619	Male	1957-08-01	2015-06-01	60	22.98398	19.27604	3.707944
2406	Male	1967-02-01	2013-11-01	50	31.24991	24.94279	6.307128
2804	Male	1981-02-01	2012-11-01	36	44.02389	32.69222	11.331667
2561	Male	1956-05-01	2016-08-01	61	22.20399	18.71200	3.491990
1554	Male	1953-05-01	2012-12-01	64	19.89530	17.00778	2.887513
1297	Female	1952-06-01	2013-11-01	65	21.84454	18.50471	3.339836
1756	Male	1976-07-01	2013-08-01	41	39.37704	30.01357	9.363474
2082	Female	1956-08-01	2016-04-01	61	25.20701	20.93058	4.276423
1999	Male	1987-04-01	2012-06-01	30	49.61711	35.70449	13.912618
1597	Male	1964-12-01	2012-11-01	53	28.67511	23.23497	5.440134
1221	Male	1956-02-01	2016-02-01	61	22.20399	18.71200	3.491990
1455	Male	1985-11-01	2016-02-01	32	47.75205	34.72503	13.027014
1254	Male	1966-08-01	2012-03-01	51	30.38299	24.37334	6.009646
2159	Female	1954-11-01	2016-07-01	63	23.51243	19.72082	3.791605
2663	Male	1987-02-01	2016-05-01	30	49.61711	35.70449	13.912618
2742	Male	1963-01-01	2016-10-01	55	27.00275	22.09854	4.904209
2528	Female	1969-04-01	2015-08-01	48	36.71376	28.50006	8.213700
2270	Male	1980-04-01	2015-04-01	37	43.09224	32.16815	10.924093
2411	Female	1961-06-01	2016-07-01	56	29.53217	23.90270	5.629467
2795	Male	1954-07-01	2012-06-01	63	20.65889	17.57720	3.081685
2333	Male	1970-02-01	2014-11-01	47	33.90005	26.64924	7.250810
1725	Female	1980-10-01	2015-04-01	37	47.09538	34.48680	12.608580
2310	Female	1952-10-01	2016-11-01	65	21.84454	18.50471	3.339836
2362	Male	1967-03-01	2014-08-01	50	31.24991	24.94279	6.307128
1941	Male	1979-07-01	2014-04-01	38	42.16123	31.63793	10.523298
1641	Male	1967-09-01	2015-12-01	50	31.24991	24.94279	6.307128
1601	Female	1969-07-01	2016-12-01	48	36.71376	28.50006	8.213700
1980	Female	1975-10-01	2012-01-01	42	42.32431	31.82912	10.495189
1349	Female	1985-09-01	2016-05-01	32	51.90714	37.00933	14.897813
1256	Female	1957-08-01	2016-12-01	60	26.06227	21.53130	4.530971
2569	Male	1991-05-01	2012-10-01	26	53.33799	37.58465	15.753344
2963	Male	1965-06-01	2014-01-01	52	29.52468	23.80401	5.720665
2572	Female	1987-06-01	2015-07-01	30	53.84311	37.98199	15.861121
1286	Male	1964-06-01	2014-04-01	53	28.67511	23.23497	5.440134
1852	Male	1953-07-01	2013-01-01	64	19.89530	17.00778	2.887513
2410	Male	1982-06-01	2015-09-01	35	44.95577	33.20992	11.745846
2364	Female	1983-12-01	2013-12-01	34	49.97805	36.01644	13.961614
2880	Female	1962-09-01	2014-08-01	55	30.41084	24.48685	5.923983
1894	Female	1961-06-01	2013-11-01	56	29.53217	23.90270	5.629467
1179	Male	1984-08-01	2012-07-01	33	46.81981	34.22622	12.593590
2006	Male	1965-04-01	2013-02-01	52	29.52468	23.80401	5.720665
1497	Female	1969-03-01	2012-06-01	48	36.71376	28.50006	8.213700
2135	Male	1986-03-01	2013-02-01	31	48.68454	35.21781	13.466732
1864	Male	1953-05-01	2012-08-01	64	19.89530	17.00778	2.887513
1831	Male	1958-11-01	2014-11-01	59	23.76989	19.83863	3.931265
2678	Male	1968-09-01	2014-10-01	49	32.12526	25.51212	6.613145
2039	Female	1953-11-01	2015-02-01	64	22.67468	19.11314	3.561539
1394	Male	1989-09-01	2016-11-01	28	51.48038	36.65839	14.821998
2457	Male	1969-04-01	2014-08-01	48	33.00876	26.08105	6.927709
1599	Female	1968-08-01	2012-05-01	49	35.79615	27.93427	7.861882
2586	Male	1977-04-01	2013-04-01	40	40.30319	30.56036	9.742828
2304	Male	1988-12-01	2014-08-01	29	50.54930	36.18483	14.364470
2480	Male	1952-02-01	2014-10-01	65	19.13889	16.43812	2.700774
1668	Female	1990-12-01	2016-04-01	27	56.75570	39.40098	17.354721
2155	Male	1984-04-01	2015-04-01	33	46.81981	34.22622	12.593590
1927	Female	1963-02-01	2013-05-01	54	31.29402	25.06758	6.226447
1591	Male	1982-04-01	2016-08-01	35	44.95577	33.20992	11.745846
2752	Male	1961-02-01	2012-06-01	56	26.18034	21.53163	4.648711
2025	Male	1972-03-01	2015-04-01	45	35.70463	27.78210	7.922535
1838	Female	1956-10-01	2013-04-01	61	25.20701	20.93058	4.276423
2210	Female	1957-07-01	2014-06-01	60	26.06227	21.53130	4.530971
2864	Male	1965-08-01	2014-01-01	52	29.52468	23.80401	5.720665
2549	Male	1981-10-01	2014-02-01	36	44.02389	32.69222	11.331667
2173	Female	1979-04-01	2013-09-01	38	46.13694	33.96558	12.171356
1374	Male	1966-01-01	2016-09-01	52	29.52468	23.80401	5.720665
2885	Male	1973-02-01	2012-12-01	44	36.61644	28.34552	8.270923
1658	Male	1956-09-01	2013-06-01	61	22.20399	18.71200	3.491990
1710	Female	1973-10-01	2016-03-01	44	40.43643	30.73370	9.702724
2131	Female	1983-01-01	2016-05-01	35	49.01598	35.51220	13.503783
1822	Female	1978-07-01	2014-06-01	39	45.18015	33.43891	11.741241
1897	Male	1970-10-01	2012-07-01	47	33.90005	26.64924	7.250810
1589	Female	1992-01-01	2015-01-01	26	57.72782	39.86282	17.864997
2718	Female	1962-09-01	2015-09-01	55	30.41084	24.48685	5.923983
2682	Female	1987-04-01	2012-02-01	30	53.84311	37.98199	15.861121
2196	Male	1953-03-01	2015-06-01	64	19.89530	17.00778	2.887513
2269	Male	1956-09-01	2014-09-01	61	22.20399	18.71200	3.491990
2470	Female	1965-04-01	2012-03-01	52	33.07715	26.22130	6.855849
2532	Male	1977-10-01	2012-09-01	40	40.30319	30.56036	9.742828
1992	Male	1984-12-01	2014-02-01	33	46.81981	34.22622	12.593590
1233	Female	1987-01-01	2013-05-01	31	52.87433	37.49821	15.376123
1149	Female	1965-04-01	2013-11-01	52	33.07715	26.22130	6.855849
1285	Female	1953-05-01	2016-06-01	64	22.67468	19.11314	3.561539
1684	Male	1986-04-01	2014-04-01	31	48.68454	35.21781	13.466732
1099	Female	1959-12-01	2012-11-01	58	27.78747	22.72331	5.064159
2478	Male	1964-11-01	2012-08-01	53	28.67511	23.23497	5.440134
2024	Male	1974-08-01	2012-03-01	43	37.53308	28.90572	8.627357
2794	Female	1989-02-01	2014-09-01	28	55.78413	38.93355	16.850579
1173	Male	1969-06-01	2013-01-01	48	33.00876	26.08105	6.927709
1624	Female	1972-11-01	2015-01-01	45	39.49838	30.18008	9.318299
2037	Female	1970-02-01	2015-02-01	47	37.63676	29.06304	8.573725
2685	Male	1988-01-01	2013-12-01	30	49.61711	35.70449	13.912618
2693	Female	1974-08-01	2013-02-01	43	41.37858	31.28351	10.095069
2429	Female	1990-07-01	2012-10-01	27	56.75570	39.40098	17.354721
2855	Male	1959-03-01	2014-09-01	58	24.56376	20.40145	4.162312
2122	Female	1952-03-01	2015-05-01	65	21.84454	18.50471	3.339836
1670	Male	1980-12-01	2014-09-01	37	43.09224	32.16815	10.924093
2564	Male	1964-11-01	2013-06-01	53	28.67511	23.23497	5.440134
1813	Female	1955-01-01	2015-03-01	63	23.51243	19.72082	3.791605
1560	Male	1962-06-01	2015-08-01	55	27.00275	22.09854	4.904209
1644	Male	1967-02-01	2015-06-01	50	31.24991	24.94279	6.307128
2452	Male	1986-06-01	2012-11-01	31	48.68454	35.21781	13.466732
2992	Female	1974-03-01	2015-11-01	43	41.37858	31.28351	10.095069
2015	Male	1956-11-01	2014-12-01	61	22.20399	18.71200	3.491990
2426	Female	1954-07-01	2014-11-01	63	23.51243	19.72082	3.791605
1737	Female	1992-03-01	2013-02-01	25	58.70051	40.31916	18.381350
2875	Male	1967-06-01	2014-07-01	50	31.24991	24.94279	6.307128
1659	Female	1967-08-01	2016-01-01	50	34.88412	27.36582	7.518297
1147	Female	1973-05-01	2013-03-01	44	40.43643	30.73370	9.702724
1990	Male	1986-12-01	2012-07-01	31	48.68454	35.21781	13.466732
1744	Male	1955-07-01	2012-08-01	62	21.42884	18.14561	3.283223
2050	Female	1953-04-01	2016-11-01	64	22.67468	19.11314	3.561539
2767	Male	1963-01-01	2013-12-01	55	27.00275	22.09854	4.904209
1316	Male	1973-02-01	2014-12-01	44	36.61644	28.34552	8.270923
1563	Male	1990-05-01	2012-12-01	27	52.41005	37.12503	15.285020
2501	Female	1958-12-01	2012-07-01	59	26.92239	22.12885	4.793543
1078	Male	1971-06-01	2016-09-01	46	34.79885	27.21641	7.582439
1348	Male	1981-05-01	2016-10-01	36	44.02389	32.69222	11.331667
1585	Male	1987-08-01	2015-01-01	30	49.61711	35.70449	13.912618
2671	Male	1966-09-01	2016-04-01	51	30.38299	24.37334	6.009646
1418	Male	1984-05-01	2015-11-01	33	46.81981	34.22622	12.593590
1084	Female	1988-01-01	2014-08-01	30	53.84311	37.98199	15.861121
1050	Male	1962-04-01	2015-01-01	55	27.00275	22.09854	4.904209
1898	Male	1966-12-01	2016-01-01	51	30.38299	24.37334	6.009646
2960	Female	1958-12-01	2012-09-01	59	26.92239	22.12885	4.793543
1998	Female	1971-08-01	2016-12-01	46	38.56503	29.62309	8.941936
2341	Female	1964-07-01	2012-06-01	53	32.18258	25.64556	6.537024
2557	Female	1963-02-01	2016-01-01	54	31.29402	25.06758	6.226447
2536	Female	1970-09-01	2013-05-01	47	37.63676	29.06304	8.573725
1607	Male	1955-02-01	2013-12-01	62	21.42884	18.14561	3.283223
2106	Female	1981-07-01	2014-04-01	36	48.05512	35.00236	13.052757
2007	Female	1976-10-01	2013-06-01	41	43.27335	32.37035	10.902995
2979	Female	1968-05-01	2014-05-01	49	35.79615	27.93427	7.861882
1696	Male	1952-04-01	2013-07-01	65	19.13889	16.43812	2.700774
1531	Female	1985-10-01	2014-10-01	32	51.90714	37.00933	14.897813
1428	Male	1971-03-01	2013-03-01	46	34.79885	27.21641	7.582439
2745	Male	1958-04-01	2015-05-01	59	23.76989	19.83863	3.931265
1315	Female	1985-03-01	2013-12-01	32	51.90714	37.00933	14.897813
1490	Female	1992-09-01	2012-04-01	25	58.70051	40.31916	18.381350
1091	Male	1952-02-01	2015-05-01	65	19.13889	16.43812	2.700774
2702	Male	1979-02-01	2015-01-01	38	42.16123	31.63793	10.523298
2117	Male	1959-07-01	2012-06-01	58	24.56376	20.40145	4.162312
1143	Male	1987-12-01	2012-05-01	30	49.61711	35.70449	13.912618
2934	Female	1975-07-01	2013-10-01	42	42.32431	31.82912	10.495189
1951	Female	1990-12-01	2015-03-01	27	56.75570	39.40098	17.354721
1175	Female	1952-01-01	2015-12-01	66	21.02306	17.89648	3.126581
2636	Male	1984-02-01	2015-11-01	33	46.81981	34.22622	12.593590
2813	Male	1968-07-01	2016-03-01	49	32.12526	25.51212	6.613145
1071	Female	1982-05-01	2015-09-01	35	49.01598	35.51220	13.503783
2961	Female	1982-02-01	2014-08-01	35	49.01598	35.51220	13.503783
2367	Female	1982-11-01	2013-05-01	35	49.01598	35.51220	13.503783
2514	Male	1954-06-01	2013-09-01	63	20.65889	17.57720	3.081685
2486	Female	1977-08-01	2013-05-01	40	44.22546	32.90706	11.318398
1051	Male	1957-11-01	2012-01-01	60	22.98398	19.27604	3.707944
1570	Male	1991-06-01	2012-03-01	26	53.33799	37.58465	15.753344
1621	Female	1955-09-01	2016-10-01	62	24.35685	20.32691	4.029938
2721	Female	1984-06-01	2014-05-01	33	50.94171	36.51542	14.426291
1965	Male	1972-02-01	2015-12-01	45	35.70463	27.78210	7.922535
1654	Female	1978-12-01	2012-12-01	39	45.18015	33.43891	11.741241
2772	Male	1971-07-01	2013-08-01	46	34.79885	27.21641	7.582439
1244	Male	1980-10-01	2016-06-01	37	43.09224	32.16815	10.924093
2654	Female	1953-09-01	2013-07-01	64	22.67468	19.11314	3.561539
2768	Female	1987-10-01	2013-11-01	30	53.84311	37.98199	15.861121
2625	Female	1974-07-01	2015-09-01	43	41.37858	31.28351	10.095069
1379	Male	1975-05-01	2014-05-01	42	38.45351	29.46191	8.991602
2170	Male	1968-11-01	2016-04-01	49	32.12526	25.51212	6.613145
2634	Female	1952-10-01	2013-06-01	65	21.84454	18.50471	3.339836
1876	Female	1976-07-01	2013-03-01	41	43.27335	32.37035	10.902995
1495	Female	1984-04-01	2016-06-01	33	50.94171	36.51542	14.426291
1726	Male	1971-03-01	2015-06-01	46	34.79885	27.21641	7.582439
2901	Male	1985-07-01	2013-07-01	32	47.75205	34.72503	13.027014
2524	Female	1977-11-01	2012-12-01	40	44.22546	32.90706	11.318398
1963	Female	1989-06-01	2012-10-01	28	55.78413	38.93355	16.850579
1453	Male	1991-11-01	2012-08-01	26	53.33799	37.58465	15.753344
2592	Male	1968-11-01	2012-10-01	49	32.12526	25.51212	6.613145
1395	Male	1966-12-01	2016-12-01	51	30.38299	24.37334	6.009646
1758	Male	1992-12-01	2014-12-01	25	54.26493	38.03785	16.227085
2638	Male	1958-10-01	2014-09-01	59	23.76989	19.83863	3.931265
1478	Male	1981-03-01	2013-07-01	36	44.02389	32.69222	11.331667
1879	Male	1969-07-01	2015-09-01	48	33.00876	26.08105	6.927709
2555	Male	1970-02-01	2013-03-01	47	33.90005	26.64924	7.250810
1398	Male	1976-02-01	2014-10-01	41	39.37704	30.01357	9.363474
1650	Female	1975-08-01	2015-05-01	42	42.32431	31.82912	10.495189
1448	Female	1988-03-01	2015-09-01	29	54.81317	38.46050	16.352662
2184	Female	1989-03-01	2014-05-01	28	55.78413	38.93355	16.850579
1808	Male	1974-01-01	2013-05-01	44	36.61644	28.34552	8.270923
1936	Male	1963-07-01	2016-10-01	54	27.83441	22.66641	5.167999
1484	Female	1991-08-01	2012-08-01	26	57.72782	39.86282	17.864997
2527	Female	1960-12-01	2012-03-01	57	28.65758	23.31474	5.342832
1790	Female	1963-03-01	2012-10-01	54	31.29402	25.06758	6.226447
2512	Male	1952-03-01	2015-01-01	65	19.13889	16.43812	2.700774
1172	Male	1974-05-01	2012-03-01	43	37.53308	28.90572	8.627357
2427	Female	1982-09-01	2012-06-01	35	49.01598	35.51220	13.503783
1429	Male	1973-08-01	2012-01-01	44	36.61644	28.34552	8.270923
2493	Female	1985-08-01	2013-09-01	32	51.90714	37.00933	14.897813
1360	Male	1960-05-01	2012-08-01	57	25.36716	20.96575	4.401413
1298	Male	1968-07-01	2016-07-01	49	32.12526	25.51212	6.613145
1755	Male	1990-10-01	2012-12-01	27	52.41005	37.12503	15.285020
2763	Male	1982-12-01	2013-10-01	35	44.95577	33.20992	11.745846
2756	Female	1981-11-01	2012-02-01	36	48.05512	35.00236	13.052757
1934	Male	1982-10-01	2014-08-01	35	44.95577	33.20992	11.745846
2028	Female	1957-03-01	2012-10-01	60	26.06227	21.53130	4.530971
2306	Female	1967-10-01	2012-07-01	50	34.88412	27.36582	7.518297
2573	Female	1955-09-01	2016-02-01	62	24.35685	20.32691	4.029938
2913	Female	1963-07-01	2012-07-01	54	31.29402	25.06758	6.226447
2610	Male	1958-10-01	2015-01-01	59	23.76989	19.83863	3.931265
2639	Female	1980-09-01	2012-09-01	37	47.09538	34.48680	12.608580
2125	Male	1974-01-01	2012-04-01	44	36.61644	28.34552	8.270923
1588	Male	1992-12-01	2014-10-01	25	54.26493	38.03785	16.227085
2587	Female	1966-03-01	2012-12-01	51	33.97776	26.79481	7.182953
1577	Female	1979-10-01	2013-01-01	38	46.13694	33.96558	12.171356
1861	Female	1967-09-01	2015-10-01	50	34.88412	27.36582	7.518297
2710	Male	1976-08-01	2015-05-01	41	39.37704	30.01357	9.363474
1866	Female	1955-12-01	2016-07-01	62	24.35685	20.32691	4.029938
1750	Male	1962-03-01	2015-02-01	55	27.00275	22.09854	4.904209
1714	Female	1957-02-01	2013-10-01	60	26.06227	21.53130	4.530971
2260	Female	1958-02-01	2014-04-01	59	26.92239	22.12885	4.793543
1058	Female	1980-09-01	2016-08-01	37	47.09538	34.48680	12.608580
1270	Female	1956-02-01	2016-01-01	61	25.20701	20.93058	4.276423
2017	Male	1952-01-01	2015-12-01	66	18.39063	15.86909	2.521532
2324	Female	1992-11-01	2012-09-01	25	58.70051	40.31916	18.381350
2683	Female	1955-06-01	2013-02-01	62	24.35685	20.32691	4.029938
2247	Male	1983-12-01	2016-11-01	34	45.88772	33.72121	12.166509
1264	Female	1968-05-01	2014-03-01	49	35.79615	27.93427	7.861882
1203	Male	1991-02-01	2012-03-01	26	53.33799	37.58465	15.753344
1494	Male	1986-03-01	2015-05-01	31	48.68454	35.21781	13.466732
2063	Female	1964-03-01	2013-10-01	53	32.18258	25.64556	6.537024
1475	Male	1983-01-01	2013-01-01	35	44.95577	33.20992	11.745846
2509	Male	1957-05-01	2016-02-01	60	22.98398	19.27604	3.707944
2377	Male	1988-09-01	2013-07-01	29	50.54930	36.18483	14.364470
2261	Female	1969-08-01	2015-06-01	48	36.71376	28.50006	8.213700
2788	Male	1961-06-01	2012-01-01	56	26.18034	21.53163	4.648711
1055	Female	1968-11-01	2013-06-01	49	35.79615	27.93427	7.861882
1513	Male	1952-12-01	2013-11-01	65	19.13889	16.43812	2.700774
1082	Female	1969-03-01	2016-12-01	48	36.71376	28.50006	8.213700
1269	Female	1966-10-01	2014-04-01	51	33.97776	26.79481	7.182953
2100	Male	1983-03-01	2016-03-01	34	45.88772	33.72121	12.166509
1365	Male	1973-04-01	2013-11-01	44	36.61644	28.34552	8.270923
1610	Male	1991-02-01	2013-05-01	26	53.33799	37.58465	15.753344
2703	Male	1980-05-01	2015-12-01	37	43.09224	32.16815	10.924093
1178	Female	1984-03-01	2015-07-01	33	50.94171	36.51542	14.426291
1848	Female	1978-11-01	2013-11-01	39	45.18015	33.43891	11.741241
2844	Female	1961-04-01	2012-05-01	56	29.53217	23.90270	5.629467
2285	Male	1962-01-01	2013-12-01	56	26.18034	21.53163	4.648711
1045	Female	1957-12-01	2014-11-01	60	26.06227	21.53130	4.530971
1537	Female	1968-11-01	2014-12-01	49	35.79615	27.93427	7.861882
1747	Female	1968-10-01	2012-02-01	49	35.79615	27.93427	7.861882
1248	Male	1989-06-01	2016-07-01	28	51.48038	36.65839	14.821998
1317	Male	1960-05-01	2016-02-01	57	25.36716	20.96575	4.401413
2317	Female	1978-02-01	2012-05-01	39	45.18015	33.43891	11.741241
2227	Male	1981-04-01	2016-10-01	36	44.02389	32.69222	11.331667
2653	Male	1960-04-01	2015-03-01	57	25.36716	20.96575	4.401413
2254	Female	1965-02-01	2016-03-01	52	33.07715	26.22130	6.855849
2468	Female	1972-10-01	2014-06-01	45	39.49838	30.18008	9.318299
1354	Female	1982-05-01	2013-12-01	35	49.01598	35.51220	13.503783
1509	Female	1967-03-01	2012-04-01	50	34.88412	27.36582	7.518297
1854	Male	1962-01-01	2013-10-01	56	26.18034	21.53163	4.648711
1028	Female	1979-11-01	2016-07-01	38	46.13694	33.96558	12.171356
2985	Male	1952-09-01	2016-12-01	65	19.13889	16.43812	2.700774
2935	Female	1989-05-01	2014-12-01	28	55.78413	38.93355	16.850579
1235	Female	1984-09-01	2014-02-01	33	50.94171	36.51542	14.426291
2385	Male	1971-06-01	2014-09-01	46	34.79885	27.21641	7.582439
2446	Female	1972-05-01	2015-06-01	45	39.49838	30.18008	9.318299
1158	Male	1971-09-01	2013-06-01	46	34.79885	27.21641	7.582439
2878	Female	1983-04-01	2012-05-01	34	49.97805	36.01644	13.961614
2224	Male	1989-12-01	2015-05-01	28	51.48038	36.65839	14.821998
2453	Male	1957-08-01	2012-10-01	60	22.98398	19.27604	3.707944
2861	Female	1956-10-01	2012-09-01	61	25.20701	20.93058	4.276423
2627	Female	1968-03-01	2016-12-01	49	35.79615	27.93427	7.861882
2986	Female	1982-11-01	2014-09-01	35	49.01598	35.51220	13.503783
1909	Male	1954-11-01	2015-03-01	63	20.65889	17.57720	3.081685
2315	Female	1978-08-01	2013-06-01	39	45.18015	33.43891	11.741241
2232	Male	1970-10-01	2013-04-01	47	33.90005	26.64924	7.250810
1765	Male	1962-08-01	2013-05-01	55	27.00275	22.09854	4.904209
1639	Male	1952-11-01	2016-04-01	65	19.13889	16.43812	2.700774
1425	Female	1982-12-01	2012-04-01	35	49.01598	35.51220	13.503783
1266	Male	1985-01-01	2014-10-01	33	46.81981	34.22622	12.593590
1030	Male	1955-09-01	2016-06-01	62	21.42884	18.14561	3.283223
1590	Female	1966-06-01	2012-10-01	51	33.97776	26.79481	7.182953
1799	Male	1957-11-01	2016-11-01	60	22.98398	19.27604	3.707944
1657	Male	1980-09-01	2013-06-01	37	43.09224	32.16815	10.924093
2681	Male	1988-10-01	2014-07-01	29	50.54930	36.18483	14.364470
2215	Female	1962-07-01	2015-12-01	55	30.41084	24.48685	5.923983
2384	Male	1982-01-01	2013-03-01	36	44.02389	32.69222	11.331667
2018	Female	1954-09-01	2013-11-01	63	23.51243	19.72082	3.791605
1214	Male	1973-11-01	2013-07-01	44	36.61644	28.34552	8.270923
1538	Male	1977-10-01	2013-07-01	40	40.30319	30.56036	9.742828
1195	Female	1971-07-01	2016-06-01	46	38.56503	29.62309	8.941936
2373	Male	1966-12-01	2014-12-01	51	30.38299	24.37334	6.009646
1679	Male	1990-09-01	2014-09-01	27	52.41005	37.12503	15.285020
1713	Male	1978-05-01	2015-08-01	39	41.23142	31.10193	10.129493
2476	Male	1971-01-01	2012-03-01	47	33.90005	26.64924	7.250810
2997	Female	1972-07-01	2015-12-01	45	39.49838	30.18008	9.318299
1952	Male	1962-10-01	2012-02-01	55	27.00275	22.09854	4.904209
1265	Male	1984-02-01	2015-10-01	33	46.81981	34.22622	12.593590
2943	Female	1966-02-01	2015-11-01	51	33.97776	26.79481	7.182953
2251	Male	1957-12-01	2016-12-01	60	22.98398	19.27604	3.707944
2440	Male	1983-12-01	2014-03-01	34	45.88772	33.72121	12.166509
2716	Male	1957-12-01	2013-02-01	60	22.98398	19.27604	3.707944
2781	Female	1976-11-01	2013-03-01	41	43.27335	32.37035	10.902995
1776	Male	1991-02-01	2012-09-01	26	53.33799	37.58465	15.753344
1929	Male	1976-03-01	2014-02-01	41	39.37704	30.01357	9.363474
2820	Female	1980-04-01	2014-05-01	37	47.09538	34.48680	12.608580
1015	Female	1962-02-01	2015-09-01	55	30.41084	24.48685	5.923983
2853	Male	1986-06-01	2012-01-01	31	48.68454	35.21781	13.466732
2728	Female	1985-02-01	2012-08-01	32	51.90714	37.00933	14.897813
2593	Male	1991-10-01	2015-12-01	26	53.33799	37.58465	15.753344
1605	Male	1955-08-01	2013-01-01	62	21.42884	18.14561	3.283223
2927	Male	1967-04-01	2013-12-01	50	31.24991	24.94279	6.307128
2255	Male	1992-07-01	2015-09-01	25	54.26493	38.03785	16.227085
1991	Female	1959-10-01	2014-01-01	58	27.78747	22.72331	5.064159
1535	Male	1981-01-01	2014-10-01	37	43.09224	32.16815	10.924093
1474	Male	1989-11-01	2016-03-01	28	51.48038	36.65839	14.821998
1307	Male	1992-04-01	2013-09-01	25	54.26493	38.03785	16.227085
2510	Female	1991-03-01	2013-03-01	26	57.72782	39.86282	17.864997
1424	Female	1983-04-01	2014-11-01	34	49.97805	36.01644	13.961614
1907	Male	1966-07-01	2013-04-01	51	30.38299	24.37334	6.009646
1620	Female	1964-11-01	2012-03-01	53	32.18258	25.64556	6.537024
2554	Male	1966-11-01	2016-09-01	51	30.38299	24.37334	6.009646
1883	Male	1957-06-01	2013-09-01	60	22.98398	19.27604	3.707944
2141	Female	1983-10-01	2012-08-01	34	49.97805	36.01644	13.961614
1824	Male	1983-10-01	2013-04-01	34	45.88772	33.72121	12.166509
1435	Male	1970-10-01	2015-10-01	47	33.90005	26.64924	7.250810
2083	Male	1968-05-01	2012-01-01	49	32.12526	25.51212	6.613145
1291	Female	1955-11-01	2014-05-01	62	24.35685	20.32691	4.029938
1739	Female	1968-09-01	2013-10-01	49	35.79615	27.93427	7.861882
2504	Female	1962-03-01	2016-07-01	55	30.41084	24.48685	5.923983
2558	Male	1962-04-01	2014-02-01	55	27.00275	22.09854	4.904209
1227	Female	1962-02-01	2016-10-01	55	30.41084	24.48685	5.923983
2094	Male	1957-09-01	2015-01-01	60	22.98398	19.27604	3.707944
1582	Female	1953-03-01	2015-05-01	64	22.67468	19.11314	3.561539
1024	Male	1980-04-01	2014-02-01	37	43.09224	32.16815	10.924093
2938	Female	1976-08-01	2012-11-01	41	43.27335	32.37035	10.902995
1826	Female	1955-03-01	2016-03-01	62	24.35685	20.32691	4.029938
2471	Female	1987-06-01	2014-05-01	30	53.84311	37.98199	15.861121
1231	Male	1952-03-01	2013-04-01	65	19.13889	16.43812	2.700774
2903	Male	1955-09-01	2013-07-01	62	21.42884	18.14561	3.283223
1502	Male	1977-12-01	2015-07-01	40	40.30319	30.56036	9.742828
1168	Female	1987-04-01	2015-04-01	30	53.84311	37.98199	15.861121
1578	Male	1975-08-01	2016-07-01	42	38.45351	29.46191	8.991602
1373	Female	1978-11-01	2012-11-01	39	45.18015	33.43891	11.741241
1388	Female	1979-02-01	2013-03-01	38	46.13694	33.96558	12.171356
1702	Male	1990-03-01	2015-03-01	27	52.41005	37.12503	15.285020
2394	Female	1957-10-01	2015-04-01	60	26.06227	21.53130	4.530971
1228	Female	1970-08-01	2016-02-01	47	37.63676	29.06304	8.573725
1552	Female	1992-08-01	2015-08-01	25	58.70051	40.31916	18.381350
2741	Female	1960-10-01	2014-03-01	57	28.65758	23.31474	5.342832
1902	Male	1962-08-01	2012-03-01	55	27.00275	22.09854	4.904209
1798	Female	1992-11-01	2012-04-01	25	58.70051	40.31916	18.381350
2537	Male	1971-12-01	2016-07-01	46	34.79885	27.21641	7.582439
2189	Male	1978-08-01	2015-09-01	39	41.23142	31.10193	10.129493
2660	Male	1965-10-01	2014-10-01	52	29.52468	23.80401	5.720665
2659	Female	1979-09-01	2016-12-01	38	46.13694	33.96558	12.171356
1604	Female	1965-01-01	2013-03-01	53	32.18258	25.64556	6.537024
1498	Female	1964-06-01	2016-09-01	53	32.18258	25.64556	6.537024
2188	Male	1988-11-01	2015-06-01	29	50.54930	36.18483	14.364470
2508	Male	1960-11-01	2012-08-01	57	25.36716	20.96575	4.401413
2673	Male	1979-10-01	2016-03-01	38	42.16123	31.63793	10.523298
2374	Male	1963-07-01	2012-09-01	54	27.83441	22.66641	5.167999
2456	Male	1988-04-01	2016-03-01	29	50.54930	36.18483	14.364470
1282	Male	1979-05-01	2014-09-01	38	42.16123	31.63793	10.523298
2640	Male	1958-05-01	2014-04-01	59	23.76989	19.83863	3.931265
1553	Male	1969-09-01	2012-05-01	48	33.00876	26.08105	6.927709
1432	Female	1977-03-01	2013-06-01	40	44.22546	32.90706	11.318398
2409	Female	1965-05-01	2015-07-01	52	33.07715	26.22130	6.855849
2957	Male	1976-04-01	2016-02-01	41	39.37704	30.01357	9.363474
1540	Male	1992-02-01	2015-10-01	25	54.26493	38.03785	16.227085
1918	Male	1967-03-01	2012-06-01	50	31.24991	24.94279	6.307128
2891	Female	1983-04-01	2014-07-01	34	49.97805	36.01644	13.961614
1068	Male	1959-02-01	2012-03-01	58	24.56376	20.40145	4.162312
1019	Female	1970-11-01	2014-06-01	47	37.63676	29.06304	8.573725
1817	Female	1977-11-01	2012-03-01	40	44.22546	32.90706	11.318398
1510	Female	1961-01-01	2013-10-01	57	28.65758	23.31474	5.342832
2400	Male	1979-10-01	2015-10-01	38	42.16123	31.63793	10.523298
2035	Male	1964-05-01	2016-01-01	53	28.67511	23.23497	5.440134
2174	Male	1983-03-01	2015-08-01	34	45.88772	33.72121	12.166509
2936	Female	1952-03-01	2013-10-01	65	21.84454	18.50471	3.339836
2605	Male	1973-12-01	2015-04-01	44	36.61644	28.34552	8.270923
1236	Male	1967-07-01	2012-01-01	50	31.24991	24.94279	6.307128
2857	Female	1970-02-01	2016-04-01	47	37.63676	29.06304	8.573725
2167	Male	1961-05-01	2016-07-01	56	26.18034	21.53163	4.648711
1925	Male	1955-03-01	2015-08-01	62	21.42884	18.14561	3.283223
2764	Male	1957-09-01	2015-04-01	60	22.98398	19.27604	3.707944
2449	Female	1961-11-01	2013-10-01	56	29.53217	23.90270	5.629467
2068	Male	1991-07-01	2012-01-01	26	53.33799	37.58465	15.753344
1314	Female	1965-12-01	2012-12-01	52	33.07715	26.22130	6.855849
2259	Female	1978-08-01	2012-06-01	39	45.18015	33.43891	11.741241
2733	Female	1972-12-01	2014-12-01	45	39.49838	30.18008	9.318299
1964	Female	1969-02-01	2013-09-01	48	36.71376	28.50006	8.213700
1159	Female	1961-12-01	2015-02-01	56	29.53217	23.90270	5.629467
2284	Female	1953-04-01	2015-04-01	64	22.67468	19.11314	3.561539
2999	Male	1960-01-01	2016-05-01	58	24.56376	20.40145	4.162312
1111	Male	1962-01-01	2015-04-01	56	26.18034	21.53163	4.648711
1072	Female	1959-07-01	2013-07-01	58	27.78747	22.72331	5.064159
1579	Female	1978-04-01	2012-11-01	39	45.18015	33.43891	11.741241
2466	Male	1968-08-01	2015-07-01	49	32.12526	25.51212	6.613145
1986	Male	1979-08-01	2016-04-01	38	42.16123	31.63793	10.523298
2110	Male	1955-04-01	2012-04-01	62	21.42884	18.14561	3.283223
1692	Female	1967-10-01	2013-01-01	50	34.88412	27.36582	7.518297
1366	Female	1958-05-01	2013-12-01	59	26.92239	22.12885	4.793543
2376	Male	1955-11-01	2012-01-01	62	21.42884	18.14561	3.283223
1280	Female	1963-10-01	2014-08-01	54	31.29402	25.06758	6.226447
2383	Male	1960-02-01	2016-08-01	57	25.36716	20.96575	4.401413
1309	Female	1965-06-01	2015-05-01	52	33.07715	26.22130	6.855849
1997	Male	1964-09-01	2012-12-01	53	28.67511	23.23497	5.440134
2250	Male	1982-05-01	2013-06-01	35	44.95577	33.20992	11.745846
2299	Female	1966-02-01	2013-12-01	51	33.97776	26.79481	7.182953
1682	Female	1990-06-01	2013-01-01	27	56.75570	39.40098	17.354721
2919	Male	1976-05-01	2015-04-01	41	39.37704	30.01357	9.363474
1637	Female	1992-03-01	2012-02-01	25	58.70051	40.31916	18.381350
1564	Male	1991-08-01	2012-07-01	26	53.33799	37.58465	15.753344
1862	Female	1956-08-01	2016-07-01	61	25.20701	20.93058	4.276423
2983	Female	1954-07-01	2014-03-01	63	23.51243	19.72082	3.791605
1488	Female	1987-12-01	2014-01-01	30	53.84311	37.98199	15.861121
2162	Male	1957-10-01	2012-03-01	60	22.98398	19.27604	3.707944
2145	Male	1966-12-01	2015-01-01	51	30.38299	24.37334	6.009646
1743	Female	1990-01-01	2016-10-01	28	55.78413	38.93355	16.850579
2391	Male	1953-05-01	2013-11-01	64	19.89530	17.00778	2.887513
1160	Male	1954-10-01	2015-11-01	63	20.65889	17.57720	3.081685
2126	Male	1954-05-01	2016-09-01	63	20.65889	17.57720	3.081685
2931	Male	1987-10-01	2016-06-01	30	49.61711	35.70449	13.912618
1757	Female	1988-11-01	2015-06-01	29	54.81317	38.46050	16.352662
2915	Male	1963-12-01	2016-06-01	54	27.83441	22.66641	5.167999
2541	Female	1964-02-01	2012-09-01	53	32.18258	25.64556	6.537024
1905	Female	1976-10-01	2016-04-01	41	43.27335	32.37035	10.902995
2276	Female	1964-11-01	2015-09-01	53	32.18258	25.64556	6.537024
1830	Female	1976-03-01	2016-03-01	41	43.27335	32.37035	10.902995
2545	Male	1969-02-01	2012-07-01	48	33.00876	26.08105	6.927709
1622	Male	1988-05-01	2012-06-01	29	50.54930	36.18483	14.364470
2542	Male	1958-07-01	2015-04-01	59	23.76989	19.83863	3.931265
2958	Male	1988-11-01	2013-06-01	29	50.54930	36.18483	14.364470
2817	Male	1987-10-01	2014-01-01	30	49.61711	35.70449	13.912618
1463	Male	1990-09-01	2016-03-01	27	52.41005	37.12503	15.285020
2405	Female	1954-07-01	2012-03-01	63	23.51243	19.72082	3.791605
2652	Female	1992-12-01	2012-09-01	25	58.70051	40.31916	18.381350
2152	Male	1966-05-01	2012-04-01	51	30.38299	24.37334	6.009646
1763	Male	1978-03-01	2013-03-01	39	41.23142	31.10193	10.129493
2604	Male	1964-04-01	2014-12-01	53	28.67511	23.23497	5.440134
2730	Male	1952-02-01	2015-07-01	65	19.13889	16.43812	2.700774
1718	Female	1955-03-01	2016-08-01	62	24.35685	20.32691	4.029938
2228	Male	1991-01-01	2015-02-01	27	52.41005	37.12503	15.285020
2325	Female	1982-05-01	2014-05-01	35	49.01598	35.51220	13.503783
1561	Male	1982-06-01	2016-09-01	35	44.95577	33.20992	11.745846
1640	Male	1952-12-01	2012-09-01	65	19.13889	16.43812	2.700774
2899	Male	1973-06-01	2015-01-01	44	36.61644	28.34552	8.270923
2401	Male	1961-07-01	2013-01-01	56	26.18034	21.53163	4.648711
2005	Male	1980-02-01	2012-12-01	37	43.09224	32.16815	10.924093
1749	Male	1963-09-01	2016-08-01	54	27.83441	22.66641	5.167999
1027	Female	1991-05-01	2014-01-01	26	57.72782	39.86282	17.864997
1452	Female	1972-03-01	2012-10-01	45	39.49838	30.18008	9.318299
2206	Female	1957-05-01	2015-04-01	60	26.06227	21.53130	4.530971
2339	Male	1974-05-01	2013-04-01	43	37.53308	28.90572	8.627357
2658	Male	1959-11-01	2015-05-01	58	24.56376	20.40145	4.162312
2272	Male	1979-10-01	2013-01-01	38	42.16123	31.63793	10.523298
1523	Female	1991-05-01	2013-10-01	26	57.72782	39.86282	17.864997
2622	Male	1987-09-01	2013-12-01	30	49.61711	35.70449	13.912618

patients %>% ggplot(aes(x = diff_exp)) + geom_histogram(bins = 18, color = 'white', fill = 'lightblue')

patients %>% ggplot(aes(x = age, y = diff_exp)) + geom_jitter(aes(color = gender)) + ylab("Years") + ggtitle("Difference in Life Expectancy (Years)")

Export results into SQL server database Analyst_Project.

QuestionA <- tibble(Id = patients$id,
                                 LifeExp = patients$life_exp,
                                 LifeExpMed = patients$life_exp_med,
                                 LifeExpDiff = patients$diff_exp)

# write into .csv
write_csv(x = QuestionA,
          path = "QuestionA.csv",
          append = FALSE)

Estimate Future Cost of Medication

Question B) (5 points) What is the total expected future cost of the medication for each patient, assuming they take it for the rest of their life? Hint: this is not equivalent to the cost of the medication over the life expectancy.

We’ll take a similar approach and work with the expected future medical cost per the Life Table LifeExp and use this as a look-up table to get each patient’s expected future medical cost due to this prescription.

First let’s do a quick data validation.

# quick data validation

med.by.month <- medication %>% pivot_wider(names_from = year, values_from = cost)
med.by.month %>% qkable()

month	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036	2037	2038	2039	2040	2041	2042	2043	2044	2045	2046	2047	2048	2049	2050	2051	2052	2053	2054	2055	2056	2057	2058	2059	2060	2061	2062	2063	2064	2065	2066	2067	2068	2069	2070	2071	2072	2073	2074	2075	2076	2077	2078	2079	2080	2081	2082	2083	2084	2085	2086	2087	2088	2089	2090	2091	2092	2093	2094	2095	2096	2097	2098	2099	2100	2101	2102	2103	2104	2105	2106	2107	2108	2109	2110	2111	2112	2113	2114	2115	2116	2117	2118	2119	2120	2121	2122	2123	2124	2125	2126	2127	2128	2129	2130	2131	2132	2133	2134	2135	2136	2137	2138
1	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
2	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
3	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
4	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
5	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
6	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
7	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
8	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
9	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
10	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
11	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983
12	306	321	337	353	370	388	406	425	445	466	487	509	532	556	580	606	632	659	688	717	747	778	810	844	878	913	950	988	1027	1067	1108	1150	1194	1239	1286	1333	1382	1433	1485	1538	1592	1649	1706	1766	1826	1889	1952	2018	2085	2154	2224	2296	2370	2446	2523	2602	2683	2766	2851	2937	3025	3116	3208	3302	3398	3496	3596	3698	3801	3907	4016	4126	4238	4352	4468	4587	4707	4830	4955	5082	5211	5342	5476	5612	5750	5890	6032	6177	6324	6473	6624	6777	6933	7091	7251	7414	7578	7745	7915	8086	8260	8436	8614	8795	8977	9162	9350	9539	9731	9924	10120	10319	10519	10722	10927	11134	11343	11554	11767	11983	11983

constants <- c() # vector of years that have the same medication price for each month in that year
different <- c()
for (years in names(med.by.month)) {
  if ( var(med.by.month[[years]]) == 0 ) { # variance of 0 implies all equal valued
    constants <- c(constants, years)
  } else {
    different <- c(different, years)
  }
}

if (length(constants) == ncol(med.by.month) - 1) {
  print(paste("For each year between",head(constants,1), "and", tail(constants,1), "the cost of medication is the same for each month."))
  
  # only keep medical costs by year
  medCostTable <- medication %>% pivot_wider(names_from = month, values_from = cost) %>% mutate(cost = `1`) %>% select(c("year", "cost"))
} else {
  print("The years", different, "have a change in medication cost somewhere mid-year!")
}

## [1] "For each year between 2018 and 2138 the cost of medication is the same for each month."

medCostTable %>% qkable()

year	cost
2018	306
2019	321
2020	337
2021	353
2022	370
2023	388
2024	406
2025	425
2026	445
2027	466
2028	487
2029	509
2030	532
2031	556
2032	580
2033	606
2034	632
2035	659
2036	688
2037	717
2038	747
2039	778
2040	810
2041	844
2042	878
2043	913
2044	950
2045	988
2046	1027
2047	1067
2048	1108
2049	1150
2050	1194
2051	1239
2052	1286
2053	1333
2054	1382
2055	1433
2056	1485
2057	1538
2058	1592
2059	1649
2060	1706
2061	1766
2062	1826
2063	1889
2064	1952
2065	2018
2066	2085
2067	2154
2068	2224
2069	2296
2070	2370
2071	2446
2072	2523
2073	2602
2074	2683
2075	2766
2076	2851
2077	2937
2078	3025
2079	3116
2080	3208
2081	3302
2082	3398
2083	3496
2084	3596
2085	3698
2086	3801
2087	3907
2088	4016
2089	4126
2090	4238
2091	4352
2092	4468
2093	4587
2094	4707
2095	4830
2096	4955
2097	5082
2098	5211
2099	5342
2100	5476
2101	5612
2102	5750
2103	5890
2104	6032
2105	6177
2106	6324
2107	6473
2108	6624
2109	6777
2110	6933
2111	7091
2112	7251
2113	7414
2114	7578
2115	7745
2116	7915
2117	8086
2118	8260
2119	8436
2120	8614
2121	8795
2122	8977
2123	9162
2124	9350
2125	9539
2126	9731
2127	9924
2128	10120
2129	10319
2130	10519
2131	10722
2132	10927
2133	11134
2134	11343
2135	11554
2136	11767
2137	11983
2138	11983

medCostTable %>% ggplot(aes(x = year, y = cost)) + geom_point(color = 'lightblue') + ggtitle("Monthly Prescription Medication Cost") + ylab("$USD")

medCostTable %>% mutate(yeardiff = lead(cost) - cost) %>%  ggplot(aes(x = year, y = yeardiff)) + geom_point(color = 'grey') + ggtitle("Annual Increase in Monthly Prescription Medication Cost") + ylab("$USD")

For some intuition behind the next functions, consider a patient with current age \(a\) and let’s follow her medical cost through until she is 120 years old. During the next year \([a, a+1)\), she has a q(1) probability of dying mid-year (7/1/2018 and hence incurring half a year’s medication cost 6 * medcost(1)) and a 1 - q(1) probability of surviving until the next year (incurring the full year’s medical cost 12 * medcost(1)). Then in the next year, conditional upon surviving 2018, the expected total cost follows a similar structure. In total, we have:

\[ \mathbb{E}(X) = \sum_{x = 1}^{\infty} \left[ \left( \prod_{i=1}^{x-1} (1 - q(i)) \right) \left( 6q(x) C(x) + 12 (1 - q(x)) C(x) \right) \right] \]

where \(C(x)\) is the vector medcost(x) and the assumption that the maximum age is 120 makes this a finite sum that we can program.

# function to get expected Medical Cost
getMedCost <- function(age, qx_med, medCostTable = medCostTable) {
  # age : patient's current age (float or integer)
  # qx_med : probability of dying within the next year; assumed to happen at the year's midpoint (7/1/20XX) 
  # medCostTable : monthly medication prescription cost per year, starting 2018 (current)
  
  age <- as.integer(age) 
  n <- 120 - age # number of years "left" POSSIBLE to live until 120
  q.vec <- tail(qx_med, n) 
  cost.vec <- head(medCostTable$cost, n)
  
  projCost <- 0 # projected (estimated) cost
  tmp.p <- 1 # running product factor
  for (yr in 1:n) {
    projCost <- projCost + 
      tmp.p * (6 * q.vec[yr] * cost.vec[yr] + 12 * (1 - q.vec[yr]) * cost.vec[yr]) 
    tmp.p <- tmp.p * (1 - q.vec[yr]) # update with probability of surviving for given `yr` number of years
  }
  return(projCost)
}

estimates <- c()

for (i in 1:nrow(patients)) {
  if ( patients$gender[i] == 'Female' ) {
    estimates <- c( estimates,   
                    getMedCost(age = patients$age[i],
                  qx_med = lifeTableFemale$medprob,
                  medCostTable = medCostTable) )
  } else {
    estimates <- c( estimates,   
                    getMedCost(age = patients$age[i],
                  qx_med = lifeTableMale$medprob,
                  medCostTable = medCostTable) )
  }
}

patients <- patients %>% mutate(est_med_cost = estimates)

# write into .csv
QuestionB <- tibble(Id = patients$id,
                    ExpCost = patients$est_med_cost)
write_csv(x = QuestionB,
          path = "QuestionB.csv",
          append = FALSE)

patients %>% ggplot(aes(x = age, y = est_med_cost)) + geom_jitter(aes(color = gender)) + ylab("Estimated Cost \n($USD)") + ggtitle("Estimated Future Medication Cost Per Patient")

Relationship between difference in life expectancy and estimated medical cost:

patients %>% ggplot(aes(x = diff_exp, y = est_med_cost)) + geom_jitter(aes(color = gender)) + ylab("Estimated Medical Cost \n($USD)") + xlab("Difference in Life Expectancy: \n Unmedicated vs Medicated") + ggtitle("Relationship between Difference in Unmedicated/Medicated \n Life Expectancy and Estimated Medical Cost")

medcost.lm <- patients %>% lm(formula = est_med_cost ~ diff_exp)

plot(medcost.lm)

medcost.lm$coefficients

## (Intercept)    diff_exp 
##    17127.32    27099.36

Surgery

Now let’s consider the patientsSurgery dataset with surgical trial results for 200 patients out of the patients table.

• id: patient’s identification number
• dos: date on which the patient received surgery
• surgery: success = 1, failure = 0

We would like to predict (the probability of) successful surgery based on gender and age_at_surgery.

genders <- c()
dobs <- c()

for (i in 1:nrow(patientsSurgery)) {
  curr.idx <- match(patientsSurgery$id[i], patients$id)
  genders <- c(genders, patients$gender[curr.idx])
  dobs <- c(dobs, patients$dob[curr.idx])
}
dobs <- as_date(dobs) # back to date format

# age at surgery 
age_at_surgery <- interval(dobs, patientsSurgery$dos) %/% years(1)


patientsSurgery <- patientsSurgery %>% mutate(gender = genders,
                           age_at_surgery = age_at_surgery)

Age at surgery appears to be

# patientsSurgery %>% filter(surgery == 1) %>% ggplot(aes(x = age_at_surgery)) +
#   geom_histogram(bins = (max(age_at_surgery) - min(age_at_surgery) + 1),
#                  color = 'white',
#                  aes(fill = gender)) + facet_grid(gender ~ .) + ggtitle("Successful Surgeries")
# 
# patientsSurgery %>% filter(surgery == 0) %>% ggplot(aes(x = age_at_surgery)) +
#   geom_histogram(bins = (max(age_at_surgery) - min(age_at_surgery) + 1),
#                  color = 'white',
#                  aes(fill = gender)) + facet_grid(gender ~ .) + ggtitle("Failed Surgeries")

# patientsSurgery  %>% ggplot(aes(x = age_at_surgery)) +
#   geom_histogram(bins = (max(age_at_surgery) - min(age_at_surgery) + 1),
#                  color = 'white',
#                  aes(fill = gender)) + facet_grid(surgery ~ gender) + ggtitle("Age Distribution of Successful \n vs. Failed Surgeries") 


# comparing this way is a bit better visually
patientsSurgery  %>% ggplot(aes(x = age_at_surgery)) +
  geom_histogram(bins = (max(age_at_surgery) - min(age_at_surgery) + 1),
                 color = 'white',
                 aes(fill = gender)) + facet_grid(gender ~ surgery) + ggtitle("Age Distribution of Successful (1) vs. Failed (0) Surgeries") 

There are some data points at the ends; however, with how sparse those are, there is no reason to believe that a classifier like an SVM would perform significantly better than logistic regression as we will do later.

patientsSurgery  %>% ggplot(aes(x = age_at_surgery)) +
  geom_density(kernel = "gaussian",
                 color = 'white',
                 aes(fill = gender)) + facet_grid(gender ~ surgery) + ggtitle("Smoothened Density of Successful (1) vs. Failed (0) Surgeries") 

Especially among males, there is good reason to believe the relationship between age_at_surgery is nonlinear. Notice this violation is relaxed if we remove the ‘outliers’ of younger males. Then a linear fit actually may not be so bad.

We also have no reason to choose a combined model of all patients with gender as a predictor, so we choose to instead fit two separate models for each gender on the single predictor age_at_surgery.

Before answering Question A of part II, let’s naively try an OLS linear regression.

males <- patientsSurgery %>% filter(gender == 'Female')
females <- patientsSurgery %>% filter(gender == 'Male') 

ols_male_fit <- males %>% lm(formula = surgery ~ age_at_surgery)
ols_female_fit <- females %>% lm(formula = surgery ~ age_at_surgery)

# summary()

plot(ols_male_fit)

plot(males$age_at_surgery, ols_male_fit$residuals)

plot(ols_female_fit)

# patientsSurgery %>% filter(gender == 'Female') %>% scatter.smooth(x = age_at_surgery, y = surgery)

plot(x = males$age_at_surgery, y = males$surgery) + lines(x = males$age_at_surgery, predict(ols_male_fit), col = "red") + title("Linear Prediction (males)")

## integer(0)

plot(x = females$age_at_surgery, y = females$surgery) + lines(x = females$age_at_surgery, predict(ols_female_fit), col = "red") + title("Linear Prediction (females)")

## integer(0)

Generally, OLS linear regression is inappropriate for a classication system (success/failure) of the surgical procedure. Predicting this linear regression could perhaps be interpreted as \(f(x) >= 0.5 \implies \text{success}\) and correspondingly \(f(x) < 0.5 \implies \text{failure}\), but notice that this simply yields a cut-off age_at_surgery.

Linear regression is based on the ‘standard statistical model’:

\[ Y = \beta_0 + \sum_{i = 1}^n [ \beta_i x_i + e_i ] , \ \text{where } \mathbb{E}(e_i) = 0 \text{ and Var}(e_i) = \sigma^2 \]

It turns out this approach could work because our response variable (success/failure) is binary; however, for some ages we have a probability outside of \([0,1]\) (negative or above 1)! This would be difficult to explain.

cutoff.male <- (0.5 - ols_male_fit$coefficients[1]) / ols_male_fit$coefficients[2]
cutoff.female <- (0.5 - ols_female_fit$coefficients[1]) / ols_female_fit$coefficients[2]

plot(x = males$age_at_surgery, y = males$surgery) + lines(x = males$age_at_surgery, predict(ols_male_fit), col = "red") + title("Linear Prediction (males) with cutoff (grey)") + abline(v = cutoff.male, col = "grey", lwd = 5, lty = 2)

## integer(0)

plot(x = females$age_at_surgery, y = females$surgery) + lines(x = females$age_at_surgery, predict(ols_female_fit), col = "red") + title("Linear Prediction (females) with cutoff (grey)") + abline(v = cutoff.female, col = "grey", lwd = 5, lty = 2)

## integer(0)

Understanding the patients who tried the surgery:

patientsSurgery %>% ggplot(aes(x = age_at_surgery)) + geom_histogram(aes(fill = gender), bins = 10, color = "white", position = "stack") + ggtitle('Distribution of Attempted Surgeries')

For ages 25~30, there is a much higher proportion of males attempting surgery than females.

patientsSurgery %>% ggplot(aes(x = dos)) + geom_histogram(bins = 10, color = 'white', fill = 'lightblue')

Unlikely, but let’s see if there’s any seasonality in the outcomes.

stackToMonth <- function(vec) {
  # vec : vector of months from 1 ~ 12 in arbitrary order
  tmp <- c()
  for (i in 1:12) {
    tmp <- c(tmp, sum(vec == i))
  }
  return(tmp)
}

# plot seasons
success.f <- month(females %>% filter(surgery == 1) %>% pull(dos))
success.m <- month(males %>% filter(surgery == 1) %>% pull(dos))
failure.f <- month(females %>% filter(surgery == 0) %>% pull(dos))
failure.m <- month(males %>% filter(surgery == 0) %>% pull(dos))


byseason <- tibble(id = 1:12,
                   month = month.abb,
                   success.f = stackToMonth(success.f),
                   failure.f = stackToMonth(failure.f),
                   success.m = stackToMonth(success.m),
                   failure.m = stackToMonth(failure.m)) 
  
  
byseason %>% ggplot(aes(x = month, y = log((success.f + success.m) / (failure.f + failure.m)), base = 2)) + geom_point(color = 'red') + ggtitle("Success/Failure ratio for each month") + ylab("Success/Failure")

Good, just as we expected. Still good to check.

Part II - Question 2:

(10 points) Fit an appropriate model; use Likelihood Ratio Tests for model selection.

We’ll fit to logistic regression which is a canonical model for univariate classification problems like these. For train-test splitting, we don’t need a validation dataset since we’ll be tuning the logistic function via MLE to our training data.

# train-test split
set.seed(20202020)
n <- nrow(patientsSurgery)
shufflerows <- sample(n)
patientsSurgery <- patientsSurgery[shufflerows,]

# ps = patientsSurgery
ps.f <- patientsSurgery %>% filter(gender == 'Female')
ps.m <- patientsSurgery %>% filter(gender == 'Male')

ps.f.train <- tail(ps.f, floor(0.8 * nrow(ps.f)))
ps.f.test <- head(ps.f, ceiling(0.2 * nrow(ps.f)))

ps.m.train <- tail(ps.m, floor(0.8 * nrow(ps.m)))
ps.m.test <- head(ps.m, ceiling(0.2 * nrow(ps.m)))

Prediction results for females:

ps.f.fit <- ps.f.train %>% glm(formula = surgery ~ age_at_surgery, 
                               family = binomial)

ps.f.train.result <- ifelse( predict.glm(ps.f.fit, 
                          type = "response") >= 0.5,
                      1,
                      0)

ps.f.test.result <- ifelse( predict.glm(ps.f.fit, 
                          newdata = ps.f.test,
                          type = "response") >= 0.5,
                      1,
                      0)

print(paste("Training accuracy for Females with 80-20 Train-test split is", 
            (sum(ps.f.train.result == ps.f.train$surgery) / nrow(ps.f.train)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Training accuracy for Females with 80-20 Train-test split is 73.33 %"

print(paste("Test accuracy for Females with 80-20 Train-test split is", 
            (sum(ps.f.test.result == ps.f.test$surgery) / nrow(ps.f.test)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Test accuracy for Females with 80-20 Train-test split is 89.47 %"

plot(ps.f.fit)

summary(ps.f.fit)

## 
## Call:
## glm(formula = surgery ~ age_at_surgery, family = binomial, data = .)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.0053  -0.8597  -0.5092   0.8827   1.9720  
## 
## Coefficients:
##                Estimate Std. Error z value Pr(>|z|)    
## (Intercept)     4.11715    1.11855   3.681 0.000233 ***
## age_at_surgery -0.09376    0.02470  -3.796 0.000147 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 103.959  on 74  degrees of freedom
## Residual deviance:  85.453  on 73  degrees of freedom
## AIC: 89.453
## 
## Number of Fisher Scoring iterations: 4

Prediction results for males:

ps.m.fit <- ps.m.train %>% glm(formula = surgery ~ age_at_surgery, family = binomial)

ps.m.train.result <- ifelse( predict.glm(ps.m.fit, 
                          type = "response") >= 0.5,
                      1,
                      0)

ps.m.test.result <- ifelse( predict.glm(ps.m.fit, 
                          newdata = ps.m.test,
                          type = "response") >= 0.5,
                      1,
                      0)

print(paste("Training accuracy for Males with 80-20 Train-test split is", 
            (sum(ps.m.train.result == ps.m.train$surgery) / nrow(ps.m.train)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Training accuracy for Males with 80-20 Train-test split is 77.38 %"

print(paste("Test accuracy for Males with 80-20 Train-test split is", 
            (sum(ps.m.test.result == ps.m.test$surgery) / nrow(ps.m.test)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Test accuracy for Males with 80-20 Train-test split is 86.36 %"

plot(ps.f.fit)

summary(ps.f.fit)

## 
## Call:
## glm(formula = surgery ~ age_at_surgery, family = binomial, data = .)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.0053  -0.8597  -0.5092   0.8827   1.9720  
## 
## Coefficients:
##                Estimate Std. Error z value Pr(>|z|)    
## (Intercept)     4.11715    1.11855   3.681 0.000233 ***
## age_at_surgery -0.09376    0.02470  -3.796 0.000147 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 103.959  on 74  degrees of freedom
## Residual deviance:  85.453  on 73  degrees of freedom
## AIC: 89.453
## 
## Number of Fisher Scoring iterations: 4

Logistic regression: Genders Combined

Let’s try a combined model on the table patientsSurgery with age_at_surgery and gender.

set.seed(202002)
combined.train <- patientsSurgery[sample(nrow(patientsSurgery)),] # shuffle again
combined.test <- head(combined.train, ceiling(nrow(patientsSurgery) * 0.2))
combined.train <- tail(combined.train, floor(nrow(patientsSurgery) * 0.8))

combined.fit <- combined.train %>% glm(formula = surgery ~ age_at_surgery + gender,
                                       family = binomial)

combined.train.result <- ifelse( predict.glm(combined.fit, 
                          type = "response") >= 0.5,
                      1,
                      0)

combined.test.result <- ifelse( predict.glm(combined.fit, 
                          newdata = combined.test,
                          type = "response") >= 0.5,
                      1,
                      0)

print(paste("Training accuracy for COMBINED MODEL with 80-20 Train-test split is", 
            (sum(combined.train.result == combined.train$surgery) / length(combined.train.result)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Training accuracy for COMBINED MODEL with 80-20 Train-test split is 76.24 %"

print(paste("Test accuracy for COMBINED MODEL with 80-20 Train-test split is", 
            (sum(combined.test.result == combined.test$surgery) / length(combined.test.result)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Test accuracy for COMBINED MODEL with 80-20 Train-test split is 84.99 %"

plot(combined.fit)

summary(combined.fit)

## 
## Call:
## glm(formula = surgery ~ age_at_surgery + gender, family = binomial, 
##     data = .)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.0831  -0.7907  -0.4387   0.7643   2.2511  
## 
## Coefficients:
##                Estimate Std. Error z value Pr(>|z|)    
## (Intercept)     4.57309    0.79196   5.774 7.72e-09 ***
## age_at_surgery -0.10520    0.01739  -6.051 1.44e-09 ***
## genderMale     -0.18594    0.37557  -0.495    0.621    
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 221.58  on 159  degrees of freedom
## Residual deviance: 171.58  on 157  degrees of freedom
## AIC: 177.58
## 
## Number of Fisher Scoring iterations: 3

As this was requested in the prompt, let’s look at a LRT for our combined model. Compare combined.fit that is trained on age_at_surgery and gender against a new fit combined.fit2 that is only trained on age_at_surgery.

combined.fit2 <- combined.train %>% glm(formula = surgery ~ age_at_surgery,
                                        family = binomial)

combined.result2 <- ifelse( predict.glm(combined.fit2, 
                          newdata = combined.test,
                          type = "response") >= 0.5,
                      1,
                      0)

combined.train.result2 <- ifelse( predict.glm(combined.fit2,
                          type = "response") >= 0.5,
                      1,
                      0)


print(paste("Test accuracy for the combined model 2 with 80-20 Train-test split is", 
            (sum(combined.result2 == combined.test$surgery) / nrow(combined.test)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Test accuracy for the combined model 2 with 80-20 Train-test split is 82.49 %"

print(paste("Training accuracy for the combined model 2 with 80-20 Train-test split is", 
            (sum(combined.train.result2 == combined.train$surgery) / nrow(combined.train)) %/% 0.0001 * 0.0001 * 100, 
            "%" ))

## [1] "Training accuracy for the combined model 2 with 80-20 Train-test split is 76.87 %"

anova(combined.fit2, combined.fit, test = "LRT") # same as test = "Chisq" (GOF test)

## Analysis of Deviance Table
## 
## Model 1: surgery ~ age_at_surgery
## Model 2: surgery ~ age_at_surgery + gender
##   Resid. Df Resid. Dev Df Deviance Pr(>Chi)
## 1       158     171.82                     
## 2       157     171.58  1  0.24498   0.6206

So with a \(p\)-value of 0.6206, gender is not significant in our model, but we had slightly better test accuracy (84.99% vs 82.49%) when using both variables. However, why not just use two separate models; one for each gender? Remember this gave testing accuracies

# root mean square error
RMSE <- mean((combined.test.result - combined.test$surgery)^2) %>% sqrt()
RMSE # relatively high for the domain of 0 ~ 1, but I'm not sure how much to read into this.

## [1] 0.3872983

summary(ps.f.fit) # females

## 
## Call:
## glm(formula = surgery ~ age_at_surgery, family = binomial, data = .)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.0053  -0.8597  -0.5092   0.8827   1.9720  
## 
## Coefficients:
##                Estimate Std. Error z value Pr(>|z|)    
## (Intercept)     4.11715    1.11855   3.681 0.000233 ***
## age_at_surgery -0.09376    0.02470  -3.796 0.000147 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 103.959  on 74  degrees of freedom
## Residual deviance:  85.453  on 73  degrees of freedom
## AIC: 89.453
## 
## Number of Fisher Scoring iterations: 4

summary(ps.m.fit) # males

## 
## Call:
## glm(formula = surgery ~ age_at_surgery, family = binomial, data = .)
## 
## Deviance Residuals: 
##     Min       1Q   Median       3Q      Max  
## -2.1553  -0.6679  -0.3463   0.6149   2.2546  
## 
## Coefficients:
##                Estimate Std. Error z value Pr(>|z|)    
## (Intercept)     5.33883    1.20010   4.449 8.64e-06 ***
## age_at_surgery -0.12997    0.02705  -4.805 1.55e-06 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## (Dispersion parameter for binomial family taken to be 1)
## 
##     Null deviance: 114.104  on 83  degrees of freedom
## Residual deviance:  77.997  on 82  degrees of freedom
## AIC: 81.997
## 
## Number of Fisher Scoring iterations: 4

Further direction with this investigation: Applying prediction model to each age.

• We can perhaps perform some calculations between the cost-benefit tradeoff of surgery at \(\$X\)

# all.females <- patients %>% filter(gender == 'Female')  %>% select(id, gender, age, diff_exp, est_med_cost)
# all.males <- patients %>% filter(gender == 'Male') %>% select(id, gender, age, diff_exp, est_med_cost)

# predict.females <- ifelse(predict.glm(object = ps.f.fit, newdata = all.females, type = 'response') >= 0.5, 1, 0)
# predict.males <- ifelse(predict(object = ps.m.fit, newdata = all.females, type = 'response') >= 0.5, 1, 0)

# all.females <- all.females %>% mutate(predictedSurgery = predict.females)
# all.males <- all.males %>% mutate(predictedSurgery = predict.males)

# length(ps.f.fit$data$age_at_surgery)
# length(16:90)


agerange <- tibble(x = 16L:90L, age_at_surgery = 16L:90L) # considered for surgery

# predicted probability of success
predProbs <- tibble(age = agerange$age_at_surgery,
                    f = predict.glm(object = ps.f.fit, newdata = agerange, type = 'response'),
                    m = predict.glm(object = ps.m.fit, newdata = agerange, type = 'response'))

predProbs.longer <- predProbs %>% pivot_longer(- age, names_to = "gender", values_to = "prob")

predProbs.longer %>% ggplot(aes(x = age, y = prob)) + geom_point(aes(color = gender)) + ggtitle("Predicted Probability of Successful Surgery")

Surgical operations such as lumbar fusion surgery for the lower back can cost up to $150,000. Assuming this cost, we might be interested in when we would ‘accept’ a currently medicated patient’s request for surgery instead of lifelong medication.

cost_of_surgery <- 80000 # arbitrary number


benefit.f <- c()
benefit.m <- c()
for (i in 16:90) {
  benefit.f <- c(benefit.f, getMedCost(i, qx_med = lifeTableFemale$medprob, medCostTable) * predProbs$f[i-15] - cost_of_surgery )
  benefit.m <- c(benefit.m, getMedCost(i, qx_med = lifeTableMale$medprob, medCostTable) * predProbs$m[i-15] - cost_of_surgery )
}



# cost_benefit <- predProbs %>% mutate(benefit.f = getMedCost(age, qx_med = lifeTableFemale$medprob, medCostTable = medCostTable) * f - cost_of_surgery,
#                                      benefit.m = getMedCost(age, qx_med = lifeTableMale$medprob, medCostTable = medCostTable) * m - cost_of_surgery)

costbenefit <-  predProbs %>% 
  mutate(f = benefit.f, m = benefit.m) %>% 
  # select(age, f, m) %>%
  pivot_longer(- age, names_to = "gender", values_to = "benefit")

costbenefit %>% ggplot(aes(x = age, y = benefit)) + geom_jitter(aes(color = gender)) + geom_vline(xintercept = 46, color = 'lightblue') + geom_vline(xintercept = 50, color = 'lightpink', show.legend = TRUE) + ggtitle("Projected Cost-Benefit Tradeoff of Surgery vs Lifetime Medication Cost")

End of document. Thank you for reading. Please see .pptx for a neater presentation of the above code and visualizations.